Problem with Macros

[Lynda]

Thanks to Dave Peterson I was able to set up my survey form. I am now trying
to insert a submit button with a macro to have the survey forms return
directly to me once completed. I used Ron de Bruin’s ‘send the
ActiveWorkbook’. I set all this up at home then emailed the sheet to myself
at work. When I opened the macro at work it appeared to have created another
email macro which was in a hidden workbook. Even though I couldn’t find it I
managed to work around it. Anyway I emailed my survey to a work mate so we
could do a test run using the submit button attached to the macro created by
Ron. Perfect, everything worked fine the sheet came back to me as I had
wanted.
Because of the type of information I needed to collect from different areas
I commenced building further survey forms (60 in all). When I went back into
work this morning I sent another survey to the same colleague for another
trial and the submit button would not work with the comment that it couldn’t
find the macro. I had assigned the button to the macro. I sent another with
the same result. The macro was there but it seemed as though the macro had
detached itself from the button. I sent the form home to myself so I could
work on it and when I opened it up at home there were no macro’s at all they
had all completely disappeared. I am sorry this is so long and drawn out but
I wanted to give you as much info as possible. Any help would be greatly
appreciated.

 

[Joel]

You are dealing with differrent fire-wals at work and at home. the macro at
home may of been removed by your server at hmoe or some software running on
your PC.

I'm not sure why the button detached itself from the macro. This could be a
firewall or a programming bug. Can't tell.

when you write macros using multiple workbooks open at the same time make
sure you are writing to the correct workbook. Refere to the workbook with
the macro using This workbbok. Other workbooks set a variable to the other
workbook like this

workbooks.add
set newbk = activeworkbook ' the new workbook always becomes the active
'workbook


or

workbooks.open filename:=MyBook.xls
set newbk = activeworkbook


the refer to the newbk in all your code

with newbk
.sheets("Sheet1").Range("A1")

end with

 

[Lynda]

Thank you Joel, i wasn't aware of the multiple workbook issue, i will check
that. And you could be right about the firewall as well as i work in a
government department. I am very frustrated as i have created so many sheets
and it appears i may have to go through and attach the macro to them all
again. These surveys are being sent to executives who always seem to have an
excuse as to why they don't have time to do these things so i thought if i
made it as simple as possible so all they had to do was click a few buttons
then maybe i could get them to respond. My other problem is i am being pushed
by a deadline to get these things out. I will try your macros at work rather
than at home so i don't end up with the same problem. Thank you again.