Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to show specified sheets in list box
I've cloned and adapted a vba code and designed a userform that does the
following: Displays in a userform list box all the worksheets in an active workbook and the number of rows in each sheet that contain data. When I double click on a sheet name in the list box, or highlight it and then click OK, it takes me to the specified sheet. Problem: I want the userform list box to display all the sheets in the workbook except those that I specify in the code. For example, my workbook contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform list box to display all the sheets except sheet 3. What do I need to insert and where? Here's the code: Option Explicit Public OriginalSheet As Object Private Sub CommandButton1_Click() OriginalSheet.Activate Unload Me End Sub Private Sub UserForm_Initialize() Dim SheetData() As String Dim ShtCnt As Integer Dim ShtNum As Integer Dim Sht As Object Dim ListPos As Integer Set OriginalSheet = ActiveSheet ShtCnt = ActiveWorkbook.Sheets.Count ReDim SheetData(1 To ShtCnt, 1 To 4) ShtNum = 1 For Each Sht In ActiveWorkbook.Sheets If Sht.Name = ActiveSheet.Name Then _ ListPos = ShtNum - 1 SheetData(ShtNum, 1) = Sht.Name Select Case TypeName(Sht) Case "Worksheet" SheetData(ShtNum, 2) = _ Application.CountA(Sht.Range("a3:a65000")) End Select ShtNum = ShtNum + 1 Next Sht With ListBox1 .ColumnWidths = "162 pt;9 pt" .List = SheetData .ListIndex = ListPos End With End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Call OKButton_Click End Sub Private Sub OKButton_Click() Dim UserSheet As Object Set UserSheet = Sheets(ListBox1.Value) If UserSheet.Visible Then UserSheet.Activate Else If UserSheet.Visible = False Then UserSheet.Visible = True UserSheet.Activate End If End If UserSheet.Activate Unload Me End Sub Thanks cavasta |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to show specified sheets in list box
Didin't have time to look at your whole code, but it looks to me like
it could be done a little cleaner. WIll try to look at it later. Until then, this will skip over Sheet3 For Each sht In ActiveWorkbook.Sheets If sht.Name < "Sheet3" Then If sht.Name = ActiveSheet.Name Then _ ListPos = ShtNum - 1 SheetData(ShtNum, 1) = sht.Name Select Case TypeName(sht) Case "Worksheet" SheetData(ShtNum, 2) = _ Application.CountA(sht.Range("a3:a65000")) End Select ShtNum = ShtNum + 1 End If Next sht cavasta wrote: I've cloned and adapted a vba code and designed a userform that does the following: Displays in a userform list box all the worksheets in an active workbook and the number of rows in each sheet that contain data. When I double click on a sheet name in the list box, or highlight it and then click OK, it takes me to the specified sheet. Problem: I want the userform list box to display all the sheets in the workbook except those that I specify in the code. For example, my workbook contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform list box to display all the sheets except sheet 3. What do I need to insert and where? Here's the code: Option Explicit Public OriginalSheet As Object Private Sub CommandButton1_Click() OriginalSheet.Activate Unload Me End Sub Private Sub UserForm_Initialize() Dim SheetData() As String Dim ShtCnt As Integer Dim ShtNum As Integer Dim Sht As Object Dim ListPos As Integer Set OriginalSheet = ActiveSheet ShtCnt = ActiveWorkbook.Sheets.Count ReDim SheetData(1 To ShtCnt, 1 To 4) ShtNum = 1 For Each Sht In ActiveWorkbook.Sheets If Sht.Name = ActiveSheet.Name Then _ ListPos = ShtNum - 1 SheetData(ShtNum, 1) = Sht.Name Select Case TypeName(Sht) Case "Worksheet" SheetData(ShtNum, 2) = _ Application.CountA(Sht.Range("a3:a65000")) End Select ShtNum = ShtNum + 1 Next Sht With ListBox1 .ColumnWidths = "162 pt;9 pt" .List = SheetData .ListIndex = ListPos End With End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Call OKButton_Click End Sub Private Sub OKButton_Click() Dim UserSheet As Object Set UserSheet = Sheets(ListBox1.Value) If UserSheet.Visible Then UserSheet.Activate Else If UserSheet.Visible = False Then UserSheet.Visible = True UserSheet.Activate End If End If UserSheet.Activate Unload Me End Sub Thanks cavasta |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code to show specified sheets in list box
Thanks JW. I guessed it was a bit untidy but because I'd cloned it and
adapted it for my own purposes I was a bit unsure which bits I could take out (I've already taken out quite a lot of it). I've had a quick play around with your suggestion and it looks good. Thanks for that. How would I go about specifying more than one worksheet that I wanted excluding? For example, if I wanted to exclude from the list sheet 1 and sheet 3. Thanks again cavasta "JW" wrote: Didin't have time to look at your whole code, but it looks to me like it could be done a little cleaner. WIll try to look at it later. Until then, this will skip over Sheet3 For Each sht In ActiveWorkbook.Sheets If sht.Name < "Sheet3" Then If sht.Name = ActiveSheet.Name Then _ ListPos = ShtNum - 1 SheetData(ShtNum, 1) = sht.Name Select Case TypeName(sht) Case "Worksheet" SheetData(ShtNum, 2) = _ Application.CountA(sht.Range("a3:a65000")) End Select ShtNum = ShtNum + 1 End If Next sht cavasta wrote: I've cloned and adapted a vba code and designed a userform that does the following: Displays in a userform list box all the worksheets in an active workbook and the number of rows in each sheet that contain data. When I double click on a sheet name in the list box, or highlight it and then click OK, it takes me to the specified sheet. Problem: I want the userform list box to display all the sheets in the workbook except those that I specify in the code. For example, my workbook contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform list box to display all the sheets except sheet 3. What do I need to insert and where? Here's the code: Option Explicit Public OriginalSheet As Object Private Sub CommandButton1_Click() OriginalSheet.Activate Unload Me End Sub Private Sub UserForm_Initialize() Dim SheetData() As String Dim ShtCnt As Integer Dim ShtNum As Integer Dim Sht As Object Dim ListPos As Integer Set OriginalSheet = ActiveSheet ShtCnt = ActiveWorkbook.Sheets.Count ReDim SheetData(1 To ShtCnt, 1 To 4) ShtNum = 1 For Each Sht In ActiveWorkbook.Sheets If Sht.Name = ActiveSheet.Name Then _ ListPos = ShtNum - 1 SheetData(ShtNum, 1) = Sht.Name Select Case TypeName(Sht) Case "Worksheet" SheetData(ShtNum, 2) = _ Application.CountA(Sht.Range("a3:a65000")) End Select ShtNum = ShtNum + 1 Next Sht With ListBox1 .ColumnWidths = "162 pt;9 pt" .List = SheetData .ListIndex = ListPos End With End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Call OKButton_Click End Sub Private Sub OKButton_Click() Dim UserSheet As Object Set UserSheet = Sheets(ListBox1.Value) If UserSheet.Visible Then UserSheet.Activate Else If UserSheet.Visible = False Then UserSheet.Visible = True UserSheet.Activate End If End If UserSheet.Activate Unload Me End Sub Thanks cavasta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vb code to copy and list selected row to diff sheets | Excel Discussion (Misc queries) | |||
list data to show up on multiple sheets | Excel Worksheet Functions | |||
code to delete sheets not in use based on a cell's drop down list | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
mailing list zip code starting with a 0 does not show 0 | Excel Discussion (Misc queries) |