Macro for "Next" Button on User Form?

[Goth]

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

 

[carlo]

Hi Goth,

try to work with the listindex:
If cbPreview Then
Worksheets(Me.ListBox1.List(Me.ListBox1.ListIndex + 1, 0)).activate
end if

maybe you need to code a limitation, in case the last is selected.

Hope that helps

Carlo

PS: Why don't you skip the non-visible sheets while populating the
listbox? So you don't have to tell the user, that he can't look at it.
Just my 2 cents.