Hi,

I have a macro (see below), but it doesn't work. When I click a button
on a custom made toolbar, the macro is triggered. It should remember
the sheet where the button was clicked and go to the sheet "Info". When
clicking again on this button it should get back to the remembered
sheet. I also have a button on the sheet "Info", with the macro
assigned. So by clicking the button on the sheet "Info", instead of
clicking the button on the toolbar, it should also get back to the
remembered sheet.
Can someone help me to get my macro to work? Thanks in advance!


Code:
--------------------
Sub InfoWerkbalk()

Const sht1 As String = "Begroting Calc Won"
Const sht2 As String = "Begroting WVB Won"
Const sht3 As String = "Werkelijk Uitv Won"
Const sht4 As String = "Totaaloverzicht Won"
Const sht5 As String = "Begroting Calc Uti"
Const sht6 As String = "Begroting WVB Uti"
Const sht7 As String = "Werkelijk Uitv Uti"
Const sht8 As String = "Totaaloverzicht Uti"
Const sht9 As String = "Info"

If ActiveSheet.Name = sht1 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht2 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht3 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht4 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht5 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht6 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht7 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht8 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht9 Then
Sheets(""" & Asheet & """).Select
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If

Info:
Sheets("Info").Select

End Sub
--------------------


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=569806

Have you declared Asheet as a module level variable, i.e. before any macros?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"leonidas" wrote in
message ...

Hi,

I have a macro (see below), but it doesn't work. When I click a button
on a custom made toolbar, the macro is triggered. It should remember
the sheet where the button was clicked and go to the sheet "Info". When
clicking again on this button it should get back to the remembered
sheet. I also have a button on the sheet "Info", with the macro
assigned. So by clicking the button on the sheet "Info", instead of
clicking the button on the toolbar, it should also get back to the
remembered sheet.
Can someone help me to get my macro to work? Thanks in advance!


Code:
--------------------
Sub InfoWerkbalk()

Const sht1 As String = "Begroting Calc Won"
Const sht2 As String = "Begroting WVB Won"
Const sht3 As String = "Werkelijk Uitv Won"
Const sht4 As String = "Totaaloverzicht Won"
Const sht5 As String = "Begroting Calc Uti"
Const sht6 As String = "Begroting WVB Uti"
Const sht7 As String = "Werkelijk Uitv Uti"
Const sht8 As String = "Totaaloverzicht Uti"
Const sht9 As String = "Info"

If ActiveSheet.Name = sht1 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht2 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht3 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht4 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht5 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht6 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht7 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht8 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht9 Then
Sheets(""" & Asheet & """).Select
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If

Info:
Sheets("Info").Select

End Sub
--------------------


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=569806

Hi Bob,

Sorry but I don't understand what you mean. I still am a novice with
VBA.
The macro as in my previous post is all I have. "Asheet" doesn't appear
anywhere else.
Can you tell me how to solve the problem?
Thanks again!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=569806

Declare ASheet before the macro, like this

Dim Asheet As Variant

Sub InfoWerkbalk()

etc.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"leonidas" wrote in
message ...

Hi Bob,

Sorry but I don't understand what you mean. I still am a novice with
VBA.
The macro as in my previous post is all I have. "Asheet" doesn't appear
anywhere else.
Can you tell me how to solve the problem?
Thanks again!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=569806

leonidas,


declare "Asheet " outside the sub, then the name with in Asheet will keep
the name when the macro stops.

So something like:

Dim Asheet As Variant
Sub InfoWerkbalk()

Const sht1 As String = "Begroting Calc Won"
Const sht2 As String = "Begroting WVB Won"
Const sht3 As String = "Werkelijk Uitv Won"
Const sht4 As String = "Totaaloverzicht Won"
Const sht5 As String = "Begroting Calc Uti"
Const sht6 As String = "Begroting WVB Uti"
Const sht7 As String = "Werkelijk Uitv Uti"
Const sht8 As String = "Totaaloverzicht Uti"
Const sht9 As String = "Info"

etc............

Met vriendelijke groet,
Rody Meulman




"leonidas" schreef
in bericht ...

Hi,

I have a macro (see below), but it doesn't work. When I click a button
on a custom made toolbar, the macro is triggered. It should remember
the sheet where the button was clicked and go to the sheet "Info". When
clicking again on this button it should get back to the remembered
sheet. I also have a button on the sheet "Info", with the macro
assigned. So by clicking the button on the sheet "Info", instead of
clicking the button on the toolbar, it should also get back to the
remembered sheet.
Can someone help me to get my macro to work? Thanks in advance!


Code:
--------------------
Sub InfoWerkbalk()

Const sht1 As String = "Begroting Calc Won"
Const sht2 As String = "Begroting WVB Won"
Const sht3 As String = "Werkelijk Uitv Won"
Const sht4 As String = "Totaaloverzicht Won"
Const sht5 As String = "Begroting Calc Uti"
Const sht6 As String = "Begroting WVB Uti"
Const sht7 As String = "Werkelijk Uitv Uti"
Const sht8 As String = "Totaaloverzicht Uti"
Const sht9 As String = "Info"

If ActiveSheet.Name = sht1 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht2 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht3 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht4 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht5 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht6 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht7 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht8 Then
Asheet = ActiveSheet.Name
GoTo Info
Else
If ActiveSheet.Name = sht9 Then
Sheets(""" & Asheet & """).Select
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If

Info:
Sheets("Info").Select

End Sub
--------------------


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=569806