Wednesday, May 17, 2017

Selecting a worksheet from within a userform in Excel VBA

I came across this problem recently.

I had a 'Goto to sheet' button in the main sheet of an Excel workbook that contained a large number of sheets.

Pressing that button caused a userform to popup, showing a list of all the relevant sheets. This is the code behind that button

    With UserForm1
        .StartUpPosition = 0
        .Show vbModeless
    End With


The user would select a sheet from the listbox and press Ok, which would activate the selected sheet, using this code.

    Dim s As String
    s = ListBox1.Value

    Worksheets(s).Activate
    Worksheets(s).Range("B1").Activate
    Unload Me


All appeared to be working well, however, it seems that Excel showed the correct page but, internally, it was pointing to the main sheet.

(The main sheet had named A14 as 'emailAddr'. 
Clicking on A14 in the main sheet would show 'emailAddr' at the address cell, which is the cell above A1 - the one that shows the cell names...)

When VBA switched to the selected sheet, if I clicked in A14, it would show 'emailAddr' in the address cell instead of A14.

If I then manually went the previous sheet and then back to the one I wanted, it'd all be ok.


When I changed the 
    .Show 
to 
    .Show vbModeless

everything worked as it should!



ps: Comments and/or links to this article are most welcome!