Macro within Excel fixed to a specific sheet

Microsoft Excel 2003 (full product)
November 18, 2009 at 01:24:19
Specs: Windows XP
I have created a spreadsheet which has a template form so that when information is added into an input sheet it links into the relevant section within the form.

When we then select send and copy I have wrote the macro which copies the template out, range values it all, then saves it as the next reference number. It also then email the form to the relevant people which has been pre determined. Each person has been given there own password. I have then used a Macro button to bring up a form which asks for a password and when a password is entered it inputs a basic Digital signature. (This has enabled me to have a document flow system via our internal network rather than paying for an expensive document flow system). My only problem is when the form has been copied out the macro button is still linked to the original file. I have put the macro within the sheet but the button is still assigned to the original macro.

My macro's are all in the Sheet1.Object so when the sheet is copied out the macro are copied with them.

See More: Macro within Excel fixed to a specific sheet

Report •

November 18, 2009 at 15:13:16

When you say the form is sent out (e-mailed), what does the form consist of?
Is it just an Excel workbook with a sheet in a particular format (derived from your template) or do you mean that you are saving and distributing the template file, i.e. a file with the .xlt extension.
(You say that selecting Send & Copy (which runs your first macro) copies the template ... then saves it).

The macro that doesn't work on the e-mailed 'form' is the one that asks for a password. You say that your macros are stored in Sheet1.Object , but which workbook is Sheet1 part of.

Although I am not clear about the exact sequence of events and what is being sent, It sounds as though the code for the password macro is part of your base workbook and that you are distributing something derived from it. As a result what is being distributed has a command button, but the code for it did not form part of the distribution.

Without your source workbook or template open, open one of the e-mailed 'forms'.
Look at the Sheet1 object and see if there is any code there.

Please post the information and then it should be possible to suggest a sequence that will work.


Report •

November 19, 2009 at 01:15:34
Hi, Thanks for your reply sorry I have not made myself more clear, it is the first time that I have ever wrote on a forum and was finding it difficult to explain my situation.

Here goes.

The form itself is not emailed I have a workbook which is made of a data sheet, an input sheet and a form sheet. The form sheet gets copied out and saved on our network as the next reference number. The email then uses this number as the subject and the email is a template explaining where the file can be found.

2 & 3.
The macro for the password form and the digital signature is in Form.Object so when the "Form Sheet" is copied out the macro copies with it. However the button to run the macro is still assigned to the "Form.Object" within the original file.


Report •

November 19, 2009 at 14:02:40

I have looked at your issue and I suspect the problem lies with the way you copy and save the worksheet 'Form'.

I tried the following:
A Source workbook with three worksheets:
Input, Data and Form

On the Form sheet is a button labelled "Password"
The button was renamed in Properties to: PasswordButton
The code for this test was just:

Private Sub PasswordButton_Click()
Dim varMsg As Variant
varMsg = InputBox("Enter your password")
ActiveSheet.Range("A1").Value = varMsg
End Sub

On the Input sheet I had a button labelled Copy and Save
The button was renamed in Properties to: CopyAndSave
The code for this is minimal, but hopefully it shows the essential steps:

Private Sub CopyAndSave_Click()
'copy some data to the Form
Worksheets("Data").Range("A1:D10").Copy Destination:=Worksheets("Form").Range("E2")
'Copy the whole 'Form' worksheet (this method makes 
'a new workbook with the copied worksheet)
'This new workbook is the last one in the collection, 
'so access it by the number of workbooks open
'then rename it and save
Workbooks(Workbooks().Count).SaveAs Filename:="Form001.xls"
End Sub

Looking at the VB window, the SaveAndCopy code is attached to the Input sheet and the Password button code is attached to the Form sheet.

I now saved and closed the original source workbook - the one with three worksheets.

I closed the new Form001.xls workbook.
I then moved this Form workbook to a different folder (on a different drive). I closed Excel.
Then I opened Form001.xls from the new location and the Password button runs OK.

In the VB window the code is attached to the Form sheet in Form001.xls.

I think that the key is the method to copy the Form worksheet and save it as a new workbook.
Worksheets("Form").Copy copies the whole worksheet and makes the new workbook, and includes any VB code that belonged to the Form worksheet.

Hope this helps.


Report •

Related Solutions

November 20, 2009 at 02:18:06
Thank you. I had done all of what you had said my only issue was something so basic it is ridiculous.

I was using the wrong button. I was using a macro button rather than a command button. So it was not until you said use a Private Sub PasswordButton_Click that I realised.

Your advise was spot on.


Report •

Ask Question