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,

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,

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