Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining Macros on a sheet when it is saved as a new workbook
I have an application where a user can click on a specifc cell and it
pops up a TextBox with a definition. The user can then close the textbox by clicking on it. Under the VBA Project window these macros reside on the Sheet2(Report) under Microsoft Excel Objects, not under Modules folder. My dilema is when I save the sheet, and save it under a new workbook; the macro to open the TextBox works, but the macro to close it does not. It can't find the original macro. Is there a way to fix this so it will close like it does in the 'master' workbook? If so what is the code? Here are the macros: Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'displays definition with a live example If ActiveCell.Address = "$B$3" Then Cancel = True With ActiveSheet .Shapes("Group1").Visible = True End With ElseIf ActiveCell.Address = "$J$3" Then Cancel = True With ActiveSheet .Shapes("Group4").Visible = True End With End If End Sub ************* 'Closes the TextBox when the user clicks on it Sub CloseTB1() With ActiveSheet .Shapes("Group1").Visible = False End With End Sub I appreciate any help you can provide. Thank you, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining Macros on a sheet when it is saved as a new workbook
Hi Tony,
There is more than one type of TextBox. From your code I suspect that it is actually an Autoshape from the Drawing toolbar because I am assuming that you have assigned Sub CloseTB1() to run when it is clicked. Is this correct? If so, then you need to right click the shape and check that the correct workwook is displayed together with the sheet name and sub. If not, then edit it. (should be able to just select the correct sub in the dialog box). If my assumptions are not correct then need some more information on what toolbar etc that you used to create it. -- Regards, OssieMac "Tony Bender" wrote: I have an application where a user can click on a specifc cell and it pops up a TextBox with a definition. The user can then close the textbox by clicking on it. Under the VBA Project window these macros reside on the Sheet2(Report) under Microsoft Excel Objects, not under Modules folder. My dilema is when I save the sheet, and save it under a new workbook; the macro to open the TextBox works, but the macro to close it does not. It can't find the original macro. Is there a way to fix this so it will close like it does in the 'master' workbook? If so what is the code? Here are the macros: Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'displays definition with a live example If ActiveCell.Address = "$B$3" Then Cancel = True With ActiveSheet .Shapes("Group1").Visible = True End With ElseIf ActiveCell.Address = "$J$3" Then Cancel = True With ActiveSheet .Shapes("Group4").Visible = True End With End If End Sub ************* 'Closes the TextBox when the user clicks on it Sub CloseTB1() With ActiveSheet .Shapes("Group1").Visible = False End With End Sub I appreciate any help you can provide. Thank you, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining Macros on a sheet when it is saved as a new workbook
On May 19, 3:06*pm, OssieMac
wrote: Hi Tony, There is more than one type of TextBox. From your code I suspect that it is actually an Autoshape from the Drawing toolbar because I am assuming that you have assigned Sub CloseTB1() to run when it is clicked. Is this correct? If so, then you need to right click the shape and check that the correct workwook is displayed together with the sheet name and sub. If not, then edit it. (should be able to just select the correct sub in the dialog box). If my assumptions are not correct then need some more information on what toolbar etc that you used to create it. -- Regards, OssieMac "Tony Bender" wrote: I have an application where a user can click on a specifc cell and it pops up a TextBox with a definition. *The user can then close the textbox by clicking on it. *Under the VBA Project window these macros reside on the Sheet2(Report) under Microsoft Excel Objects, not under Modules folder. My dilema is when I save the sheet, and save it under a new workbook; the macro to open the TextBox works, but the macro to close it does not. *It can't find the original macro. Is there a way to fix this so it will close like it does in the 'master' workbook? *If so what is the code? Here are the macros: Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'displays definition with a live example If ActiveCell.Address = "$B$3" Then * * Cancel = True * * With ActiveSheet * * * * .Shapes("Group1").Visible = True * * End With *ElseIf ActiveCell.Address = "$J$3" Then * * Cancel = True * * With ActiveSheet * * * * .Shapes("Group4").Visible = True * * End With End If End Sub ************* 'Closes the TextBox when the user clicks on it Sub CloseTB1() With ActiveSheet * * .Shapes("Group1").Visible = False End With End Sub I appreciate any help you can provide. Thank you,- Hide quoted text - - Show quoted text - OssieMac, Thanks for your help with this. Yes, you were correct I made the 'TextBoxes' from the Drawing' tool bar. But the way I resolved my problem was to simply add the line: ActiveSheet.Copy and remove my old lines; 'Sheets("report").Select 'Sheets("report").Copy After:=Sheets("report") Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining Macros on a sheet when it is saved as a new workbook | Excel Programming | |||
Delete all macros if workbook is saved with another name | Excel Worksheet Functions | |||
hyperlink to sheet in workbook saved as html | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet; workbook saved as html | Excel Worksheet Functions | |||
How to execute macros not saved in a workbook | Excel Programming |