Recorded Macro Problem: Inconsistently Working :(

[wedzmer]

Hello Excelites!

I'd really need help with this macro code I recorded from Excel. I have 2 issues with these....

1st: The macro code is not compatible with the other codes I have with me the sheet. I dunno why but it doesn't work when I merge it inside the other macros. The cells it should be affecting are also unlocked though the other codes make the sheet locked.

2nd: The recorded macro was done twice with the same principle since when I run the macro I recorded, it won't work. So I recorded a different macro with the same function on the other set of cells. Which I would really like to make both macros to function into a single button which I assigned to since these macros are separated between two different buttons.

here's the whole set of codes I'm using it with:

Please Login or Register  to view this content.

PS:

i already labelled the first and second macro above which I have problem with.

[6StringJazzer]

What does "it doesn't work" mean here? What are you expecting it to do, and what does it do instead of what you expect? You have a Worksheet_Change sub in this module so I assume it is a worksheet module. Therefore your recorded macros should operate on that sheet.

"when I merge it inside the other macros" How are you merging it? The code you have posted does not reflect this so I can only guess at what you tried that didn't work.

[wedzmer]

Helll sir! Thanks for your reply...

What I mean that "doesn't work" are the codes for ERASE _filipino_12q command and ERASE_Q3Q4 command..

Both are recorded macros, which actually has the same principle of function, to erase values on certain cells. I recorded the second macro ERASE_Q3Q4 because for some reason. I can only make the first one work.. then when I pasted both commands insed the other set of macros, excel pops up with an error.

[6StringJazzer]

Please read over what I am trying to find out. What does it do instead of what you expect? Does it do nothing? Does it erase the wrong thing? Does it produce an error?

WHen you pasted it into other macros, what error do you get? Whenever you are asking for help with a VBA error, give the error number and text that Excel gives you, and indicate the line of code where it occurs.

Your code is specific to your file, so I can't test it without your file. Can you attach the Excel file?

[wedzmer]

What does it do instead of what you expect?

It does nothing actually, only an error warning. It's suppose to delete certain ranges of cells within the worksheet.

...what error do you get?

Error 400...

Can you attach the Excel file?

For some reason I can't attach files directly here in my reply.. every time I click on the attach icon, it doesn't show anything.. I decided to upload the file on a file uploading site.. I'm so sorry for this...

FILE HERE

The file is a compiled workbook of multiple sheets... I'm using the file for all the work I'm suppose to accomplish... The codes practically came from the site - most of it perhaps... I opted protecting the file so that my coteachers won't be able to erroneously alter any of the codes especially the macro codes since I'm not well versed with it.

The recorded codes were tested on English and Filipino worksheets....

[6StringJazzer]

We are having problems with our paperclip button. I did get your file, and I'm reviewing it. Just for reference:

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[6StringJazzer]

Your VBA is password protected. Can't do anything here.

[wedzmer]

Your VBA is password protected. Can't do anything here.

oh sorry, the password was actually included in the code above... it's 09193483966 btw...

[6StringJazzer]

You have code in many sheets. Which one is the one where you are trying this?

[wedzmer]

I wanted it to work on all the subject grading sheets, that would be around 11 sheets if I'm not mistaken... I planned to use the same type of coding on the other sheets as well, but I was already experiencing problems with the Filipino worksheet and the English worksheet... that was when I decided to ask for help.. hehe

PS
I'm trying to create a all in one school forms file so that whether I get transferred from one grade level to another, I don't need to alter my paperworks.. that's why the file is a little bit congested.

[6StringJazzer]

OK, I'm still not getting the one key piece of information: You posted a module with several Subs. You have two Subs that are recorded. You say you want to "merge it inside the other macros". You have to tell me exactly what you want your code to do here, which Sub you want to merge, and where you want to merge it.

[wedzmer]

You say you want to "merge it inside the other macros". You have to tell me exactly what you want your code to do here, which Sub you want to merge, and where you want to merge it.

Sorry if I got you confused... well basically, the two recorded macros which are named (1) ERASE_FILIPINO_12q and (2) ERASE_q3q4 actually does the same thing but on different cell ranges. I wanted to merge or make it into one single macro code to be assigned on a single button. Where in both of them act as one single macro code..

[6StringJazzer]

OK, you want to merge just those two subs into a single sub, and be able to assign it to a button. I thought you wanted to merge them into your other code.

Where is the button going to be? If you have one button, it has to have a way to indicate which sheet you want to run that code on. It can't just be the active sheet, because the sheet with the button is going to be the active sheet.

If you have a button on every sheet, you can create an ActiveX button in every sheet named CommandButtonErase and use this in every sheet:

Please Login or Register  to view this content.

Then in Module1 add this code:

Please Login or Register  to view this content.

If you instead use Forms buttons, then you can do Assign Macro and select Erase.

[wedzmer]

Where is the button going to be? If you have one button, it has to have a way to indicate which sheet you want to run that code on. It can't just be the active sheet, because the sheet with the button is going to be the active sheet.

I'm planning to have this code(s) to work on every subject grading sheet I will be using.. if you noticed on my file, I tested the recorded macros on English and Filipino subjects.

If you have a button on every sheet, you can create an ActiveX button in every sheet named CommandButtonErase and use this in every sheet:

I was thinking of putting a button on each subject sheet too.. ^_^
What is an ActiveX button by the way? (Sorry, super noob here).

And also, where do I find the Module1?

[6StringJazzer]

What is an ActiveX button by the way? (Sorry, super noob here).

When you add controls to a worksheet, go to the Developer tab and in Insert you will see two groups of controls. The top group contains Forms controls, which are the older controls (perfectly fine, just been around longer). The bottom group is the ActiveX controls. ActiveX controls have more powerful features for managing them within code, and also have many easily configured properties, so I prefer them.

And also, where do I find the Module1?

I am surprised at this question, because you have already created Module1, although there is no code in it. If you go to the VBA development window, on the left you will see your file, and underneath there are icons for Microsoft Excel Objects and Modules. If you click on Modules, you will see Module1.

[wedzmer]

I already tried the codes but I guess there was something wrong..

Please see the screenshot of the error...

ss1.png

[6StringJazzer]

Oh, whoops, that should be

Please Login or Register  to view this content.

Sorry

[wedzmer]

Just to confirm, that's for the module1 right?

[6StringJazzer]

Yes. But I have to apologize--I was reminded by one of our senior members, shg, that "Erase" is a VBA keyword and cannot be used as Sub name. You will need to name it something else (such as EraseRanges).

[wedzmer]

I've already altered the codes but unfortunately it won't work with my other codes... i mean, it errors when the other button within the sheet is used... since the sheet has a button that sets up a .protect and .unprotect command...

I also tried using the same codes merged with what you posted above but I'm still experiencing the error 1004...

IMG_0900.JPG
IMG_0901.JPG