Vbs keyboard activated userform listbox

[DenniBrink]

Hello everyone! I seeking guidance on how to create a keyboard activated macro for a userform navigation worksheet listbox. I want to be able to access the userform anywhere in the workbook with the Control + M keyboard command.

I have created the Userform listbox and dynamic range name (WorksheetLists). I need help on where in my workbook the subroutine should be placed, how it should be activated, and associated with the keyboard activated macro.

 

[My Aswer Is This]

If you want a script to activate your UserForm. You would use a script like this:

UserForm1.show

This would go into any standard module in your workbook

 

[DenniBrink]

Thank you for the suggestion! I now am able to call the WorkSheetSelector listbox with the Ctrl + Shift + M keyboard command. Now I need to write the procedure for the recall of the listed worksheets in the Listbox. There are 23-worksheets. How should the VBS procedure be written?

 

[My Aswer Is This]

I'm not sure what you mean when you say:
"I now am able to call the WorkSheetSelector listbox"

And I do not know what this means:

Now I need to write the procedure for the recall of the listed worksheets in the Listbox

 

[DenniBrink]

What I have done is created the Listbox with a Row Source Name Range. By the keyboard command Cntrl + Shift +M the Listbox will appear anywhere in the workbook. For the listbox to enable jump worksheet navigation, I need to write the VBS code.

 

[My Aswer Is This]

If you want a script to show all your sheet names in a listbox use this script:

For i = 1 To Sheets.Count
    ListBox1.AddItem Sheets(i).Name
Next

 

[DenniBrink]

Here is what I have accomplished:

Module 1 Code:
Option Explicit
Sub Userform_Show()
Userform1.Show
End Sub

This enables Userform1 to be displayed with the macro keystroke Cntrl + Shift + M.

Userform1 Code:
Private Sub CommandButton1_Click()
Dim i As Integer, sht As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
sht = ListBox1.List(i)
End If
Next i
Sheets("Main Menu").Select
Sheets("Main Menu").Activate
Sheets("HR Menu").Select
Sheets("Roster").Select
Sheets("Wkly Sched").Select
Sheets("Lists").Select
Sheets("Calendar").Select
Sheets("Matrix Menu").Select
Sheets("Crew 30").Select
Sheets("Crew 31").Select
Sheets("Crew 32").Select
Sheets("Crew 33").Select
Sheets("Sched Menu").Select
Sheets("Master Daily Sched").Select
Sheets("Sat_Assignments").Select
Sheets("Sun_Assignments").Select
Sheets("Mon_Assignments").Select
Sheets("Tue_Assignments").Select
Sheets("Wed_Assignments").Select
Sheets("Thu_Assignments").Select
Sheets("Fri_Assignments").Select
Sheets("Help Menu").Select
Sheets("How It Works").Select
Sheets("Formulas").Select
Sheets("About").Select
End
End Sub

This procedure is not working correctly. Can anyone identify what is wrong with the above VBS subroutine?

 

[My Aswer Is This]

So on your UserForm you have a listbox with all your sheet names. I that correct??

And you want to select a sheet name in the list and then that will activate the selected sheet is this correct?

 

[Norie]

How is the code not working correctly?

What is it actually mean to do?

Also, why are you jumping about sheets here?

Sheets("Main Menu").Select
Sheets("Main Menu").Activate
Sheets("HR Menu").Select
Sheets("Roster").Select
Sheets("Wkly Sched").Select
Sheets("Lists").Select
Sheets("Calendar").Select
Sheets("Matrix Menu").Select
Sheets("Crew 30").Select
Sheets("Crew 31").Select
Sheets("Crew 32").Select
Sheets("Crew 33").Select
Sheets("Sched Menu").Select
Sheets("Master Daily Sched").Select
Sheets("Sat_Assignments").Select
Sheets("Sun_Assignments").Select
Sheets("Mon_Assignments").Select
Sheets("Tue_Assignments").Select
Sheets("Wed_Assignments").Select
Sheets("Thu_Assignments").Select
Sheets("Fri_Assignments").Select
Sheets("Help Menu").Select
Sheets("How It Works").Select
Sheets("Formulas").Select
Sheets("About").Select

 

[DenniBrink]

Yes!