I want to add a button to each row in column Z. I want 1000 rows to be covered. I have 1000 macros wrote. I have copied/pasted the macro buttons which are identical so there are 1000 buttons now in each row in the column Z. But how can I quickly make each button automatically assign to a specific macro without having to individually assign a macro to each button? The macros are titled Macro1 Macro2 Macro3 and so on.
I suppose a different way of having it would be to have some sort of loop code that would say that if in column Z row 7 was clicked then macro7 would run and if column Z row 101 was clicked then macro101 would run. Is this possible?
This will do what you want but I've got to ask, what do these 1000 macros do?
Do they each do something completely different or are they basically the same/
Please Login or Register to view this content.
If posting code please use code tags, see here.
Yeah pretty much the same. All that changes is one number which goes up by 1 each time.
You mentioned using a loop code, presumably instead of the butttons, but what exactly do you mean?
What would you be looping?
Instead of the buttons or a loop how about using the worksheets before double-click event?
Please Login or Register to view this content.
Is there any way to have all the buttons say the same thing rather than Button1, Button2 Button3 etc?
Yeah that might work as well. How would I then have it that if row 50 column Z was clicked it would call Macro50?
Here's the macro below which I have to make multiple thousands of. The 0 highlighted in red is the only thing that changes with each macro. So for the macro Macro1 which I have below the value is 0. For macro Macro2 the value is 1, etc etc. Is there any easy way of making many thousands of these without manually copying pasting and changing the values by myself?
Please Login or Register to view this content.
You don't need to repeat this code thousands of times.
This macro could be assigned to all the buttons.
You could change the code similarly to work with double click.Please Login or Register to view this content.
Would I use that in conjunction with your worksheet change event macro above?
What change event macro?
This one?Originally Posted by Norie
You mentioned using a loop code, presumably instead of the butttons, but what exactly do you mean?
What would you be looping?
Instead of the buttons or a loop how about using the worksheets before double-click event?
Please Login or Register to view this content.
I tried just using macro1 but it doesn't work. Gives me an application defined or object defined error and highlights "Set rng = .Resize(.Rows.Count - 1).Offset(lngOffset)"
That isn't for the Change event it's for the BeforeDoubleClick event.
Can you attach a sample workbook where you'll be using this code?
Here you go! Thanks!
sample.xlsm
Slight error in the code.
Try this.
Please Login or Register to view this content.
That is awesome! Thank you so much for saving me so much time! What does this mean though, as it seems to make the whole thing work!
lngOffset = Val(Mid(Application.Caller, 7)) - 1
I'm going to create tonnes of buttons now! Any way to make all the buttons say the word Data, instead of saying Button1 BUtton 2 etc??
Application.Caller returns the name of the button that was clicked which will be something like Button X, where X is 1,2,3,4...1000.
We use Mid to extract the 1,2,3,4... part and to get the correct offset we subtract 1.
If you want all the buttons to have the caption Data add this in the loop that creates them.
Please Login or Register to view this content.
I just deleted column Q and tried re adding the buttons again but now it starts from 301. So in row 1 its button 301 and so on. How can I get it to start at Button 1 again like the first time?
It might be difficult and if you are going to be adding and deleting buttons quite a lot you'd be best to name them yourself.
Add this to the loop as well.
Please Login or Register to view this content.
I tried that but to no avail. It keeps continuing on. So if the last button number is 500 and I delete all the buttons and set I = 1 To 20 it will populate row 1 to 20 but the button number will start at 501.
That's not happening when I run the code, and that's after deleting the 1000 buttons quite a few times.
Where exactly did you add btn.Name?
Directly blow btn.on action
Can you post the exact code you have now?
By the way, you could avoid all this faffing about if you used the Double Click event.
Please Login or Register to view this content.
What would I need to do for the double click event to make this work?
To get what to work?
To click on a cell in column Q and for just that row to be moved to the other sheet. I think ideally, I'd prefer the button method though if you can see any reason why it's not naming the buttons how I want?
The buttons aren't being called Button 301 etc, that's their caption.
I posted code to change the captions of all the buttons to Data earlier, see post #17.
But button 301 in row 2 when clicked does nothing when it should remove row2
Can you arrach an updated workbook?
Last edited by Norie; 11-21-2012 at 07:57 PM.
I'm also sure the code can be edited to make it more efficient. If I hit the button on row 11 I want that row range A to M cut and moved to the bottom of the other sheet and where the data is cut from I want the remaining data to be moved up.
I know I used autofilter in the code I sent but I'm sure for what I need its not needed ie. theres a better , simpler way? I'll post an updated sample in a while. Thanks for your continued help Norie. Really really great help you have been.
If that's all it's doing why are you filtering?
You know the row you want to cut and you know where you want to paste to.
Please Login or Register to view this content.
And presumably in there I'll have instead of Offset(1) I'll have Offset(lngoffset) where lngOffset = Val(Mid(Application.Caller, 7)) - 1???
Last edited by ScabbyDog; 11-22-2012 at 04:20 AM.
No, that code is for the DoubleClick event.
It can be adapted for the buttons.
The Offset doesn't change because it's for the destination.
Ok, I think I'm almost there! Thankfully! I have the Buttons all inserted now and they all point to the below macro. How would I change the macro so that it incorporates the code above which doesn't use autofilter?
So when I click on say Button 50 it just removes button 50 and puts it to the bottom of the other sheet instead of auto filtering.
Please Login or Register to view this content.
Removes the button. Why?
Oops. I meant to say how to I incorporate your code above that doesn't use auto filter? I have all the buttons listed down the page. So for eg, when I click button 50 I just want it to remove that row range A:M, and put it at the bottom of the other sheet.
This is how it would work.
Please Login or Register to view this content.
Brilliant Norie! Works great. The only bad thing is that since there are lile 1500 buttons whenever I hit one of the buttons it takes about 3 seconds to remove the row. Any way of quickening it up that you know of?
Try turning off screen updating.
Please Login or Register to view this content.
That makes it slower for me Norie! Strange?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks