Exporting/Importing Sheets

[yippie_ky_yay]

Hello Forum,

I have two workbooks that I would like combined into one (rather, add the second workbook to this main one) - they both have lots of VBA, etc.

When I "move copy" over to the main one, everything works as should with the exception of my form buttons (I can't click on them - althought their code seems to have been copied correctly)

Could someone go over the safest way to combine the two? (I have 97, but access to 2000 and XP as well)

Thanks,
-Sean
This message was edited by yippie_ky_yay on 2002-09-13 11:48
This message was edited by yippie_ky_yay on 2002-09-13 12:04

 

[Damon Ostrander]

Hi Sean,

I believe the problem is simply that the code for Forms buttons resides in standard macro modules rather than in the worksheet. Since the modules and the worksheets are copied in two separate steps the assignment of the buttons to their macros is broken. The buttons simply need to be re-assigned to the macros. You can check if this is the case by right-clicking on the button, select Assign Macro, and see if it is assigned to anything. My bet is that it is not.

If this is the problem there are several possible solutions. One would be simply to swap out all the Forms buttons with Controls (ActiveX) buttons, whose Click event resides in the event code module associated with the worksheet. With these buttons when you copy the sheet you alco copy the code so the Click event assignment is not broken.

A way to make it work with the existing Forms buttons would be to add code to loop through all your Forms buttons and save their macro assignments before each worksheet is moved to the other workbook, then re-assign the buttons to their macros after the worksheet is moved. The following routines could be used to do this:

Dim MacroName() As String

Sub SaveButtonAssignments()
Dim Btn As Button
ReDim MacroName(0)
For Each Btn In ActiveSheet.Buttons
ReDim MacroName(UBound(MacroName) + 1)
MacroName(UBound(MacroName)) = Mid(Btn.OnAction, InStr(2, Btn.OnAction, "!") + 1)
Next Btn
End Sub

Sub RestoreButtonAssignments()
Dim Btn As Button
Dim i As Integer
i = 0
For Each Btn In ActiveSheet.Buttons
i = i + 1
Btn.OnAction = MacroName(i)
Next Btn
End Sub

To use these you must make sure they operate on the same worksheet before and after the worksheet and macro code have been moved.

 

[yippie_ky_yay]

Hi Damon,

thank you for replying - and your suspision was right on!

I was actually combining three workbooks - one was full of form buttons, the other with control ones (back when I was experimenting with both to see which was better).

The same type of thing was happening with the control buttons. In their case though, all that was happening was that they were renamed to their default names when they were moved to the new workbook - so I just had to change each of their names (rather tedious though).

Thanks again,

-Sean