ramohse
09-24-2013, 05:52 AM
Hello all,
Firstly, thank you all very much for your time. I am trying to create a toggle button in Developer that will switch a particular range on a series of worksheets between two sources of information. Independently the Select Case functions work properly (thanks SamT), but when integrated with the If Else statement of the toggle button I get a "Else without If" error. here is the code:
Private Sub ToggleButton1_Click()
Dim Cel As Range
Dim strReplaceWhat As String
Dim strWithWhat As String
Dim Ws As Worksheet
If ToggleButton1.Value Then
ToggleButton1.Caption = "Click for X"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr"
strReplaceWhat = "Y"
strWithWhat = "X"
Case "5yr"
strReplaceWhat = "Y"
strWithWhat = "X"
Case Else
GoTo WSNEXT
End Select
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
Else:
ToggleButton1.Caption = "Click for Y"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr"
strReplaceWhat = "X"
strWithWhat = "Y"
Case "5yr"
strReplaceWhat = "X"
strWithWhat = "Y"
Case Else
GoTo WSNEXT
End Select
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
End If
WSNEXT:
Next Ws
End Sub
For the life of me I can't figure out where my "If" breaks. I tried adding a few "End If" statments at the end just in case, and that didn't work. Any assistance that can be provided will be greatly appreciated. Thank you!
BONUS QUESTION: The "Y" database yields numbers as they would appear, the "X" database yields them in millions (Y = 1,000,000.00 vs X = 1.00). So between the two I would also have to divide by 1,000,000. I fooled around with a [C11:N37]/x , where Dim x As Integer x = 1000000, but what I don't want to happen is for every time I toggle the button the numbers compound the division. I can probably figure this out, but if anyone has any shortcuts/advice I'd appreciate it. Thanks!
Firstly, thank you all very much for your time. I am trying to create a toggle button in Developer that will switch a particular range on a series of worksheets between two sources of information. Independently the Select Case functions work properly (thanks SamT), but when integrated with the If Else statement of the toggle button I get a "Else without If" error. here is the code:
Private Sub ToggleButton1_Click()
Dim Cel As Range
Dim strReplaceWhat As String
Dim strWithWhat As String
Dim Ws As Worksheet
If ToggleButton1.Value Then
ToggleButton1.Caption = "Click for X"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr"
strReplaceWhat = "Y"
strWithWhat = "X"
Case "5yr"
strReplaceWhat = "Y"
strWithWhat = "X"
Case Else
GoTo WSNEXT
End Select
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
Else:
ToggleButton1.Caption = "Click for Y"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr"
strReplaceWhat = "X"
strWithWhat = "Y"
Case "5yr"
strReplaceWhat = "X"
strWithWhat = "Y"
Case Else
GoTo WSNEXT
End Select
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
End If
WSNEXT:
Next Ws
End Sub
For the life of me I can't figure out where my "If" breaks. I tried adding a few "End If" statments at the end just in case, and that didn't work. Any assistance that can be provided will be greatly appreciated. Thank you!
BONUS QUESTION: The "Y" database yields numbers as they would appear, the "X" database yields them in millions (Y = 1,000,000.00 vs X = 1.00). So between the two I would also have to divide by 1,000,000. I fooled around with a [C11:N37]/x , where Dim x As Integer x = 1000000, but what I don't want to happen is for every time I toggle the button the numbers compound the division. I can probably figure this out, but if anyone has any shortcuts/advice I'd appreciate it. Thanks!