LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Macro for "Next" Button on User Form?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a "Next" button to move the user to the next sheet Rob Excel Discussion (Misc queries) 2 March 29th 09 12:14 PM
"Form / Button" Macro for Unhiding Sheets Melissa Excel Discussion (Misc queries) 0 September 5th 08 06:07 PM
User form closes after opening another one -- Problem with "Unload Me"? [email protected] Excel Programming 2 January 8th 07 05:52 AM
Disable Close "X" button on User Forms Celtic_Avenger[_50_] Excel Programming 0 October 11th 04 06:25 PM
Disable Close "X" button on User Forms Celtic_Avenger[_49_] Excel Programming 2 October 11th 04 06:20 PM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"