titous

hello,

i have an excel worksheet (sheet1) that has buttons in it that have certain functions. one of the buttons copies the worksheet (sheet1) and places at the end of all the worksheets (so that then you have sheet1, sheet2):


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim totalSheets As Integer = Application.ActiveWorkbook.Sheets.Count
Me.Copy(After:=Globals.ThisWorkbook.Sheets(totalSheets))
End Sub



however, when i go to the new copied worksheet (sheet2), none of the buttons work anymore. they can't even be clicked. someone suggested that i write this:

Sheets("Sheet1").Copy After:=Sheets(1)

and so i changed my code to

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim totalSheets As Integer = Application.ActiveWorkbook.Sheets.Count
'Me.Copy(After:=Globals.ThisWorkbook.Sheets(totalSheets))
Sheets("Sheet1").Copy(After:=Globals.ThisWorkbook.Sheets(totalSheets))
End Sub

however now, it says i have to declare Sheets. what do i declare it as i'm a little confused. thanks for your help!



thanks!



Re: Visual Studio Tools for Office buttons won't work in copied excel worksheet

titous

anyone have any idea thanks!





Re: Visual Studio Tools for Office buttons won't work in copied excel worksheet

titous

i changed my code to

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim totalSheets As Integer = Application.ActiveWorkbook.Sheets.Count

Globals.ThisWorkbook.Sheets("Sheet1").Copy(After:=Globals.ThisWorkbook.Sheets(totalSheets))

End Sub

and it seems to be copying Sheet1. however, the buttons do not work on the new copied sheet. how could i fix this thanks!





Re: Visual Studio Tools for Office buttons won't work in copied excel worksheet

Geoff Darst - MSFT

Hi,

We don't support copying controls at runtime. The control is actually created by the customization, so the only way we could support this feature would involve modifying the customization at runtime--which would be undesirable for security reasons. The only thing that actually gets copied is the underlying ActiveX wrapper. On Excel this results in the control image being copied due to the way that Excel handles data caching. IIRC you won't see this behavior on Word.

Sincerely,

Geoff Darst

Microsoft VSTO Team