VBA Control Button Help

**Vittorio** · 10-26-2009, 02:20 AM

Hi,

Here is what i am currently using as code for one of my 56 or so buttons:

Private Sub CommandButton8_Click()

Dim i As String
i = "Mathematics"

    Sheets("Subjects").Select
    
    On Error GoTo ErrorHandler
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Class").CurrentPage _
        = i
     
ErrorHandler:
    
    MsgBox i & " does not exist. Please press OK and choose another button.", vbOKOnly
        
    Sheets("Summary").Select
    
    Exit Sub

End Sub

And here are the challenges i'm getting so far:

#1

The message box is only supposed to pop-up if "i" is not found on the "Subjects" worksheet. However, what i am getting is the msgbox even if "i" is found or valid. Is there something wrong with my syntax or the order it was written?

#2

How do i type the message box content so that it reads:

"Mathematics" does not exist. Please press OK and choose another button.

instead of:

Mathematics does not exist. Please press OK and choose another button.

Please note the "" marks.

#3

This is just an add-on question. For worksheets with multiple buttons, is there a way to format the code so that it works for all instead of typing it separately for each "i"?

Thanks guys. Looking forward to your help.

**D_Rennie** · 10-26-2009, 02:45 AM

hello, Try this, I will exit the sub before the error message if everthink ran fine, if not it will show the message. Also not the """" in the message itself

Private Sub CommandButton1_Click()
Dim i As String
i = "Mathematics"

    Sheets("Subjects").Select
    
On Error GoTo ErrorHandler

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Class").CurrentPage _
        = i
        
    Exit Sub
    
ErrorHandler:

    MsgBox """" & i & """" & " does not exist. Please press OK and choose another button.", vbOKOnly
        
    Sheets("Summary").Select
End Sub

**D_Rennie** · 10-26-2009, 02:49 AM

sheet loop

Private Sub UserForm_Click()
Dim AllSheets As Worksheet
For Each AllSheets In ThisWorkbook.Sheets
    MsgBox AllSheets.Name

''''' your code
dim i as string
 i = AllSheets.name
'' rest of code


Next AllSheets
End Sub