VBA multiple button to clear multiple ranges

[APML]

Hi all, I have a worksheet that I have broken down into 50 sections, within these sections I have various unprotected cells that I need to clear.

i.e In section 1, I need to clear the range I've called C_1 which refers to =Watch!XEQ11,Watch!XEV11,Watch!XEW11,Watch!XEY11,Watch!XEZ11,Watch!XEQ14,Watch!XET14,Watch!XEU14,Watch!XEV14,Watch!XEW14,Watch!XEX14,Watch!XEQ17,Watch!XEZ16,Watch!XEY18,Watch!XEZ18,Watch!XEW27,Watch!XEY27,Watch!XEW30,Watch!XEY30,Watch!XEQ32,Watch!XEQ34,Watch!XER34,Watch!XES34,Watch!XET34,Watch!XEU34

In section 2, I need to clear the range I've called C_2 which refers to
=Watch!A1,Watch!F1,Watch!G1,Watch!I1,Watch!J1,Watch!A4,Watch!D4,Watch!E4,Watch!F4,Watch!G4,Watch!H4,Watch!A7,Watch!J6,Watch!I8,Watch!J8,Watch!G17,Watch!I17,Watch!G20,Watch!I20,Watch!A22,Watch!A24,Watch!B24,Watch!C24,Watch!D24,Watch!E24

Etc, all the way up to 50. I will be using a form button to trigger the clear contents (so I'll have 50 button, 1 per section)

So my question is, do I need 50 separate VBA codes or can I have 1 vba that references the various buttons, .....am thinking something like if the button in C_1 is triggered then it would clear range C_1 contents

 

[APML]

Yep, you could use a ComboBox list, but with 50 items of very similar name, I can imagine you would possibly selct the wrong item !!!
Have a look here at creating combobox lists

Excel ComboBox Lists for Easy Data Entry

How to use an Excel ComboBox with a drop down list to make data entry easy in an Excel UserForm

www.contextures.com

Am thinking that's way beyond my skill level (which is tiny). Also thinking that it's probably no big deal just doing 50 VBA's

So if I've got this right it would go like this:

Sub C_1()
Range("C_1").ClearContents
End Sub
And I'd use a button, And use the assign macro, in this case, it would be called C_1

then for the next, I would do:
Sub C_2()
Range("C_1").ClearContents
End Sub
And I'd use another button, And use the assign macro, in this case, it would be called C_2

And so on right up to C_50

 

[Michael M]

Yes, the range to be cleared needs to reflect that ranges name....but I still think the InputBox is a better option....do you really want 50 buttons and 50 codes ???

 

[APML]

Yes, the range to be cleared needs to reflect that ranges name....but I still think the InputBox is a better option....do you really want 50 buttons and 50 codes ???

 

[APML]

Yes, the range to be cleared needs to reflect that ranges name....but I still think the InputBox is a better option....do you really want 50 buttons and 50 codes ???

I think you're right but we send this sheet to our customers, I'm concerned that they won't know what to put into the input box, that's why I was thinking a pull-down list (because I could rename the ranges from C_1 to Clear Input 1 etc) which would eliminate errors

 

[Michael M]

If I was a customer I would be more concerned that I had to choose from 1 of 50 buttons !!
Another way to approach the inputBox would be like this...Only the number needs to be inserted....change the message to suit what you think

Sub MM1()
ans = InputBox("Simply Type in the Range number you wish to clear....eg, 1 or 4 or 50 ?")
  Range("C_" & ans).ClearContents
End Sub

 

[APML]

Is there some way I could use indirect?
Sub C_1()
Range("IndirectH7").ClearContents
End Sub

The above doesn't work but is there a way to make it work?

 

[APML]

If I was a customer I would be more concerned that I had to choose from 1 of 50 buttons !!
Another way to approach the inputBox would be like this...Only the number needs to be inserted....change the message to suit what you think

Sub MM1()
ans = InputBox("Simply Type in the Range number you wish to clear....eg, 1 or 4 or 50 ?")
  Range("C_" & ans).ClearContents
End Sub

So the sheet has been broken down into 50 sections.. and I'd put a button in each section.. so they would already be in that section when they could use the clear button for that section.. but I agree it is a bit painful
The reason I can't use numbers (as suggested above) is that they wouldn't know what the number was. They put a code in as per the screen shot below
Thats why I was thinking that some how indirect could help. Something like they put the code in then xlookup find the range name to be cleared.
i.e lets say ZZZ was entered in section 2, then they enter ZZZ, xlookup comes back with C_2, which could be where the VBA gets it info from

 

[Michael M]

I have given you a number of appropriate options. If each section is numbered or identified in some way, you can use that as your range identifier as well.
Otherwise, I will see if someone else has any better ideas.

 

[APML]

I have given you a number of appropriate options. If each section is numbered or identified in some way, you can use that as your range identifier as well.
Otherwise, I will see if someone else has any better ideas.

Hey really appreciate your time and effort in helping me, funnily enough, I just found this, which does the job. Now I can enter my code, do an xlookup, to bring up the named range to be cleared .
Sub clearcell()
Range(Range("H7").Value).ClearContents
End Sub