Exporting/Importing Sheets

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Jun 25, 2002
Messages
63
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,530
Messages
5,596,700
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top