What I have is code that copies the active sheet and auto renames it based on a specific cells value. The only problem with this is that when it copies, it carries with it several codes that are not needed and end up causing problems. I recorded a macro in excel to delete several cmd buttons and then went to vba while still recording and deleted the code that I did not want, but the macro only recorded deleting the buttons not the code.
Last edited by 2funny4words; 07-16-2010 at 02:30 PM.
Check out this thread
http://www.excelforum.com/excel-prog...no-macros.html
Hi 2funny4words;
A simpler solution instead of writing code that deletes other code:
Let's say you have something like this
and when you copy the sheet, there is no Sheet10, so it crashes.
Please Login or Register to view this content.
Change it to:
and in a different modulePlease Login or Register to view this content.
Now the Worksheet_Change runs with no problem and since the other module wasn't copied with the sheet you never have to worry about it getting executed. And the Run command doesn't cause an error during compiling.Please Login or Register to view this content.
Almost all of my code is currently under sheet 1.
When I tried moving everything that I did not want copied into the modules, most or none of it wanted to work. I tried theand was able to get it to work for the on double click code but not for worksheet selection or worksheet selection change codesPlease Login or Register to view this content.
Please Login or Register to view this content.
How exactly do I make this code work for how I want? I am assuming that it copies the active sheet and then deletes all of the lines of code below a specific line number. Am I correct? If so, how do I make it start deleting code at say line 24 or 41 etc.Originally Posted by snb
Please Login or Register to view this content.
rather obvious
Please Login or Register to view this content.
do you include empty lines or just coded lines?
Last edited by shg; 07-22-2010 at 06:18 PM. Reason: deleted quote
Check VBEditor's Help on codemodule and it's properties. I can't explain better.
Also note that in order to delete code from a VBA module, you'll probably have to enable trusted access to the VBA project object model. In Excel 2007 you:
Click the Office button (top left corner of Excel window)
Click 'Excel Options'
Click 'Trust Center' at left
Click 'Trust Center Settings' at right
Click 'Macro Settings' at left
Check box for 'Trust access to the VBA project object model'
Click OK twice to return to Excel
Thanks Paul for the tip.
The code throws an 438 error "object doesn't support this property or method"
When I pasted the code in it looks like this, notice that everything past VBProject did not capitalize.
Am I missing something very obvious? I checked the help as sng suggested but it did not give anything that would help me on this.Please Login or Register to view this content.
Thanks for all of the help so far
That's probably because it's not written correctly. Try:
This will delete line 2 of code in Sheet1's code module. By leaving off the second argument (# of lines) it defaults to 1 line. If you specify .CountOfLines, but your starting line is greater than 1, you'll get an error since the code cannot delete 50 lines of code if only 50 exist and you start at line 2. (In that instance, you could use something likePlease Login or Register to view this content.
If you want to be able to see the properties and methods of the VBProject or VBComponent objects, set a reference to 'VBA for Applications' in Tools -> References (in the VB Editor window).Please Login or Register to view this content.
If you want to delete a certain procedure (macro or event) form a userform you can use it's name.
Please Login or Register to view this content.
Last edited by snb; 07-23-2010 at 08:09 AM.
Paul, I tried using the code that you posted but it gave an 1004 error "application defined or object defined error". Here is how I have it.
I also triedPlease Login or Register to view this content.
to see if it was the countoflines, or the thisworkbook that was giving me the problem but still the same error.Please Login or Register to view this content.
Is appears you're trying to delete code from a user-form? Or do you have a sheet named "form"? If it's a user-form, is "form" the actual name of it?
Here is the code that is on sheet1 "form" I deleted much of the copy special code as it is all just repitition for pasting values for about 20 cells to a logbook.
Please Login or Register to view this content.
The name for sheet1 is "form"
Sorry for the confusion
Hmm.. no matter what I do I'm getting either 1004 error or subscript out of range errors (error 9). I tried something like this, too:
Perhaps someone more fluent in interacting with code modules can assist...Please Login or Register to view this content.
Well at least it isn't just me
Just so that it is clear, I want to delete everything below the first 4 print codes. I need them to stay so that the next person to open the copied sheet can print the sheet but nothing else.
Thanks
Change the name of your sheet: Excel has 'reserved' names.
'Sheet203' will always be correct (not reserved).
The code-deleting macro should not be in the module from which code has to be deleted.
Ok,
I changed the settings so that access to vb projects is trusted like paul said I might.
I changed the name of the worksheet, and moved the code to a seperate module, and I still get a subscript out of range error.
the code that I want to delete is in sheet1 and the delete code is now in module 6
OK,
It has been a couple of months since I have had time to work on this and now I am back to it needing to finish it.
Does anyone have a suggestion on how to accomplish the following task,
remove all code from below line 37 so that only the print codes are available to the user when the copyied sheet is opened (would it work to put the print codes in a module and then delete all of the other codes?)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks