Listbox populated with Sheet Names to Hiughlight Activesheet

C

Corey ....

I have a userform, with a Listbox(Listbox1) on it.

It is populated with all(minus 1) sheet names within the workbook.
*************************
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
Dim actsh As Worksheet
Dim lngIndex As Long
With ThisWorkbook
For Each wsSheet In .Worksheets
If wsSheet.Name <> "NAVIGATION PAGE" Then
ListBox1.AddItem wsSheet.Name
End If
Next
End With
End Sub
*************************

I have the form display using the WorkSheet_Activate ruitine.
************************
Private Sub Worksheet_Activate()
If Sheet1.Range("A8").Value = "" Then Call UserformShow
End Sub
************************

But what i want to do is have the ACTIVE Sheet name HIGHLIGHTED(Selected) in
the Listbox, when the form is initialised.

How can i achieve this ?


Corey....
 
C

Corey ....

Solved.
Used:
***************************
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
Dim actsh As Worksheet
Dim lngIndex As Long
With ThisWorkbook
For Each wsSheet In .Worksheets
If wsSheet.Name <> "NAVIGATION PAGE" Then
ListBox1.AddItem wsSheet.Name
End If
Next
End With
ListBox1.Value = ActiveSheet.Name ' <===== This line here !
End Sub
****************************

Corey....
 
P

Patrick Molloy

are you using the worksheet activate code for each worksheet? if not, then
you'll always get the same sheet selected ..as that's the one opening the
userform.

an alternative could be to use the ThisWorkbook's Workbook_SheetActivate
event which fires whenever a new sheet is selected.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top