none
rangevariable not filled with the value of 'Application.InputBox' as VBA-project is secured. RRS feed

  • Question

  • I created a macro to let the user select a random worksheet at a given time.
    The macro will work well until you lock the VBA project for viewing with a password.
    The rangevariable 'RangeVanOverview' is then not filled with the value of 'Application.InputBox'.

    What am I doing wrong?
    Or is this an bug in Excel?

    I use Microsoft Office 2016

    Below is the source code:

    '=============================================

    Sub SelecteerBrondata() Dim SheetBron As Worksheet Dim OldStatusBar As String Dim RangeVanOverzicht As Range Dim Oorsprong As Range Set Oorsprong = ActiveCell 'FOUTBOODSCHAP ONVERWACHTE FOUT If 1 = 2 Then OnverwachteFout: On Error GoTo 0 'HERSTEL DE TEKST OP DE STATUSBALK Application.StatusBar = "" 'HERSTEL OVERIGE APPLICATION SETTINGS Application.ScreenUpdating = True Application.EnableEvents = True Application.EnableCancelKey = xlInterrupt MsgBox "Onverwachte fout, de macro stopt.", vbCritical, " SelecteerBrondata " Exit Sub End If 'LAAT GEBRUIKER DE BRONDATA AANWIJZEN 'TOON DE TEKST OP DE STATUSBALK Application.StatusBar = " SELECTEER EEN CEL VAN HET WERKBLAD VAN HET TE SORTEREN OVERZICHT ..." Opnieuw: Oorsprong.Activate Oorsprong.Select Set RangeVanOverzicht = Nothing On Error Resume Next 'laat 'cancel' toe Set RangeVanOverzicht = Application.InputBox(Title:=" Selecteer MET DE MUIS ... ", _ prompt:=" SELECTEER MET DE MUIS ÉÉN CEL VAN HET TE SORTEREN OVERZICHT OF DRUK OP ""ANNULEREN"" ... ", _ Default:="$A$1", Type:=8) On Error GoTo 0 If RangeVanOverzicht Is Nothing Then Exit Sub End If
    'DE VOLGENDE WERKBLADEN MOGEN NIET GESELECTEERD WOORDEN If (InStr(1, "A00A01A02A03", RangeVanOverzicht.Parent.CodeName, vbTextCompare) > 0) Then MsgBox "U hebt een onjuist werkblad geselecteerd." & RangeVanOverzicht.Parent.CodeName, vbInformation, " SelecteerBrondata" GoTo Opnieuw Else Set SheetBron = RangeVanOverzicht.Parent End If 'HERSTEL DE TEKST OP DE STATUSBALK Application.StatusBar = "" 'HERSTEL OVERIGE APPLICATION SETTINGS Application.ScreenUpdating = True Application.EnableEvents = True Application.EnableCancelKey = xlInterrupt End Sub



    Tuesday, January 8, 2019 7:17 PM

Answers

All replies

  • Could it be that you have added one or more worksheets AFTER protecting the VBA project? If so, the new sheets will not be listed in the Project Explorer pane on the left hand side of the Visual Basic Editor. Do the following:

    • Open the VBA project by providing the password.
    • In Excel, right-click each of the sheet tabs of the sheets that you added, and select 'View Code'.
    • This will add those sheets to the Project Explorer.
    • Save the workbook, then close and reopen it.

    Does the problem still occur?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 8, 2019 9:37 PM
  • No Hans, that is not the case.

    By the way, the phrase "a random worksheet at a given time" must be " random worksheet of another workbook at a given time" ...

    Tuesday, January 8, 2019 10:20 PM
  • I have just discovered that 'application.inputbox' stores the value of the cell instead of the range address, despite the value of 'Type' (=8) ...

    See the screeenshot:

    • Edited by Tuesday, January 8, 2019 10:51 PM
    Tuesday, January 8, 2019 10:43 PM
  • I'm afraid I cannot reproduce the error that you describe. I'm using Excel 2019, and the variable RangeVanOverzicht is set correctly except in the situation that I described in my previous reply...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 8, 2019 10:44 PM
  • Hans,

    From various forums I deduce that it is still a kind of 'bug' from Excel.

    For exemple https://stackoverflow.com/questions/22812235/using-vba-to-prompt-user-to-select-cells-possibly-on-different-sheet

    A comment: "There is only one problem with this code. The range address that is returned is not fully qualified if the selection is from a different sheet or different workbook. So you will have to take care of that as well"

    I found a solution (workaround) in the article on the site https://jkp-ads.com/Articles/SelectARange.asp
    This solution works with 'GetInputRange' (method 2 in the article).

    Regards Rü




    • Marked as answer by Wednesday, January 9, 2019 9:10 AM
    • Edited by Wednesday, January 9, 2019 9:19 AM
    Wednesday, January 9, 2019 9:09 AM
  • Good to hear that you were able to solve the problem!

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 9, 2019 9:50 AM