C
cavasta
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
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