Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I'm really in a hole. The following code (borrowed liberally from John
Walkenbach) runs a User Form that shows all of the sheets in a workbook. It includes an OK button to allow a user to go to a sheet selected in the list, and to "preview" each sheet when a checkbox on the form is checked. 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 ShtNum = ShtNum + 1 Next sht With ListBox1 .ColumnWidths = "220pt" .List = SheetData .ListIndex = ListPos End With End Sub ========================================= Private Sub CancelButton_Click() OriginalSheet.Activate Unload Me End Sub ========================================== Private Sub cbPreview_Click() If cbPreview Then Sheets(ListBox1.Value).Activate End Sub ========================================== Private Sub ListBox1_Click() If cbPreview Then _ Sheets(ListBox1.Value).Activate 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 MsgBox("Selected page is for form maintenance only and cannot be accessed. Please see form administrator with questions.", _ vbQuestion + vbOKOnly) = vbOK Then OriginalSheet.Activate End If End If Unload Me End Sub This works really well. I'm trying, however, to add a "Next" button to the form, so that, when the user clicks it, the next form on the list will be previewed (if the "Preview" box is checked). I've tried attaching the following code to a button I've added to the form: Private Sub NextButton_Click() ' Executed when the NextButton is clicked If cbPreview Then Sheets (ListBox1.Value + 1).Activate End Sub --But I get a "mismatch" error. I get the same error with: Private Sub NextButton_Click() ' Executed when the NextButton is clicked If cbPreview Then Sheets ((ListBox1.Value) + 1).Activate End Sub What am I doing wrong? I'd really appreciate any help--this seems like it should be a simple problem, but clearly I'm really out of my element. As usual. Goth |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Creating a "Next" button to move the user to the next sheet | Excel Discussion (Misc queries) | |||
"Form / Button" Macro for Unhiding Sheets | Excel Discussion (Misc queries) | |||
User form closes after opening another one -- Problem with "Unload Me"? | Excel Programming | |||
Disable Close "X" button on User Forms | Excel Programming | |||
Disable Close "X" button on User Forms | Excel Programming |