This blog serves as a repository of references to useful, neat or just plain cool tools, technologies, info or ideas.
I like creating new products and enhancing existing ones through innovation, with a dash of pizzazz thrown in.
The articles collected here reflect all aspects of technology, from hard-core tech to aesthetics.
You can also check out my music (original compositions) at https://play.google.com/store/music/artist?id=A6qquqenbr767npxmb3wo33itcu
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
everything worked as it should!
ps: Comments and/or links to this article are most welcome!