Thursday, September 13, 2007

Navigation into Worksheets using Listbox

You need to add a ListBox in a UserForm and copy this two simple procedures:

Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
ListBox1.AddItem sht.Name
Next sht
End Sub

Private Sub ListBox1_Click()
Sheets(ListBox1.ListIndex + 1).Activate
End Sub

Once you show the UserForm it will add the names of the sheets in the ListBox, then when you click on one of the sheets name in the ListBox it will select the corresponding sheet in the WorkBook. I hope it works

5 comments:

  1. Hi,

    I am very new to this group, and in fact this is my first post. Hope that is ok.

    Yes, the 2 macros will work, as long as Application.Screenupdating is not set to False.

    But, I don't think that *user* can get control to edit the worksheet by themselves as the control will still be with the form.

    If user needs to get control (as in viewing a worksheet normally when no macros are being run), then immediately after activation of the selected sheet, the Exit routine should be called. A sample exit routine is given below (I am assuming there is a commandbutton called ExitButton in the form):

    Private Sub ExitButton_Click()
    Application.ScreenUpdating = True
    Me.Hide
    Unload Me
    End Sub

    In case everyone here knows the above (ie. it is obvious), or this is not the intended action, kindly ignore this post. As a total newbie, I probably have jumped the gun in that case.

    Thanks & Best Regards,
    -feltra

    ReplyDelete
  2. Hallo,

    I saw multiple examples to navigate around worksheets, but without doubts, this is the best way.

    Congratulations
    Eduardo Sebastiani
    Brazil
    Edusebast_40@hotmail.com

    ReplyDelete
  3. hi this is not working. pls elaborate further..

    thanks.

    ReplyDelete
  4. thank you. my name is javhaa

    ReplyDelete
  5. Hi
    i need this ASAP i have 1 long string in 1 of the cell in the worksheet where in i have 2 segregate the datas in it to various columns as in the THe String is like
    "YES ABC NO XYZ YES AGH NO HGT" AND IT DIFFERS IN VARIOUS COLUMN WHEREIN SOME COLUMN I IS "NO ABC YES XYZ..."KINDLY HELP ME 2 WITE A MACRO ON A BUTOON CLICK I TRIED THE SOURCES BUT DIDNT WORK PLEASE HELP ME

    ReplyDelete