Hello there.
I have a worksheet change macro that will insert a picture when cell D5 is edited. My problem is that although I have it working on the sheet called "INVITE" I would like to be able to get the macro working on other sheets in the book. Is anyone able to assist per chance?
I look forward to hearing from you
Please Login or Register to view this content.
Neil Shaw
I suspect that changing:
Please Login or Register to view this content.
to:
Please Login or Register to view this content.
would generalise the code for you.
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
thanks for your reply.
Is there any way that I could perhaps list the sheets that I want the code working on? The code above will be on a separate worksheet to where the picture is inserted you see. so ideally i would like to be able to define a few sheets that this code is executed on...
A Worksheet_Change event applies to the sheet in which the code resides. You can copy and paste the code into the sheets to which it is relevant.
The alternative, if you only want one copy of the code, which is a not unreasonable requirement, is to have the code in the Workbook_SheetChange event. You could then test the ActiveSheet.Name to determine if the code applies to the sheet being processed.
Regards
You could put the main code into a seperate module - AddSignature
Then use the Workbook SheetChange eventPlease Login or Register to view this content.
Workbook Event code should be added to the workbook code module:Please Login or Register to view this content.
Copy the Excel VBA code that you want to use
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Right-click on the ThisWorkbook object, and choose View Code
Where the cursor is flashing, choose Edit | Paste
Last edited by royUK; 10-10-2011 at 02:35 AM.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Hi Roy,
Thank you so much for the suggestion, I think this seems like a very logical approach.
Unfortunately I seem to be getting a compile error on the Workbook_SheetChange module.
The line that is in red is
The error:-Please Login or Register to view this content.
"Compile error:
Expected: Then or GoTo"
Any idea how to resolve this?
Neil
can you attach the workbook
Unfortunately I am unable to upload currently, either my server is not working or the forum server upload function is not working. I may be able to try later.
Just spotted typo, should be
Check your add signature because the variable names do not match your declared namesPlease Login or Register to view this content.
Please Login or Register to view this content.
It doesn't appear to be working...
If I explain a little more, perhaps this will help, if not then I can email the sheet over?
1. The worksheet change event is triggered by cell D5 on a sheet called "INPUT"
2. Once the criteria has been selected in this cell, the worksheet change event inside "INPUT" calls a macro to include a JPG image on another sheet called "INVITE" which uses another piece of code to place the image on the invite letter.
3. I now also need to reference a number of other sheets e.g "INVITE 2", "INVITE 3" and include the JPG on them too.
If that's the case then you don't need the workbook code, but use the Input sheet change event.
You can't use the invite's events unless the sheet is active
Unfortunately I cannot get it to work. Would you be able to Inbox your email please so I can send this sheet to you?
Sorry I'm at work but you can attach it later perhaps from home
I have finally solved it.
all that I have done is defined the sheets at the start (see the section with Set ws1 etc):-
Thanks for your help.Please Login or Register to view this content.
Neil
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks