How to make VBA click on a button?

**anorris_TSAV** · 06-06-2018, 11:56 AM

So the layout, is that I have several macro-buttons on my worksheet.

the one in question inserts several new rows above it, when clicked. The macro requests the topleftcell from the button that was clicked in order to determine the location of the new rows to insert.

This button was created using the Controls > Insert > Form Controls: Button

(not the ActiveX Control button)

When I control+click on the button this macro is assigned to, I can see that it has the name "Button 13"

I have tried searching around various forums, but those have only resulted in a runtime error, or "can not find shape with that name" errors.

Anyone have any ideas?

The end-goal is to be able loop the click command 100+ times, to make sure that the naming convention built into the macro works all the way.

**1aaaaaaaaaaaaaaa** · 06-06-2018, 12:35 PM

Hi-

You can reference the VBA code the command button is calling.

e.g if the function name is CommandButton1_Click()

You can:

Please Login or Register  to view this content.

**anorris_TSAV** · 06-06-2018, 12:47 PM

I might have to try just manually activating the code associated with the button like that.

The trouble is ... that I have another macro that is copy-pasting several rows in order to create another version/placement of this button I am trying to click through VBA command

Since the name isn't ALWAYS "Button 13" I used code that didn't require a name in order to request the topleftcell value

EDIT: this button does in fact always have the same name

Please Login or Register  to view this content.

**anorris_TSAV** · 06-06-2018, 12:50 PM

I may just need to change that line of code to a defined name, and then run my test-case with the file just saved as a test file.

Since this process of clicking the button via VBA is just for troubleshooting, and not actually being used long-term in the macro.

**1aaaaaaaaaaaaaaa** · 06-06-2018, 12:52 PM

I would need to see your full implementation to have a better idea on how to solve this, there may be a much simpler way to implement what you're trying to do

**anorris_TSAV** · 06-06-2018, 12:55 PM

I could make a thinned down version of my excel and attach it here (or maybe to google drive, and put a link to it here) if you think that would help

**1aaaaaaaaaaaaaaa** · 06-06-2018, 12:56 PM

Sure, if you don't mind

Thank you sir.

**Arkadi** · 06-06-2018, 01:09 PM

anorris_TSAV, I see Poizhan is helping you and don't want to hijack his efforts, but it may help him too to know the answer to this question. You want code to click a button... and the code that the button activates is built in such a way that you don't need a name since you don't always know what the button's name is. My question would be... how can you programatically click the button (or simulate that somehow), if you don't know the name? What I mean is, how would the code that "clicks the button" know which button to click without a name?

**anorris_TSAV** · 06-06-2018, 01:10 PM

Alright, so here are the sheets and macros in-question (google drive link)

(the forum says I can't post links until i've posted a few more times so the following link will have to be broken up. Just remove the spaces and change the: "(dot)" into ".")

drive (dot) google (dot) com /file/d/1RwRB1OYDtRN7Xj-VaY3nssuZGkC5221n/view?usp=sharing

In the process of thinning it down, I think the button in question actually DOES stay the same name all the time. I think I can probably try calling that macro that is attached to the button, and allow that to request the buttons position by-name.

hhmmmmm

but the goal of the VBA simulated click, was so that I can troubleshoot my floor-naming part of the code. I think I've cleared it all up now, except for the "hundred's" range (not that that's ever going to be used, but it's nice to have templates of other projects that might use number-naming that goes that far)

**anorris_TSAV** · 06-06-2018, 01:17 PM

What I mean is, how would the code that "clicks the button" know which button to click without a name

Right, at first I was thinking I would need to have the click-code know the original name, then with a counter, be able to figure out what the current name of the bottom most button would be called

but in reviewing, the button control does indeed have a static name, and there will not be any copies of this particular button.

**Zer0Cool** · 06-06-2018, 01:29 PM

I am not able to access google drive, so unless you can post the attachment to the forums I cant look at it.

To clarify, is my understanding below correct?:

You have multiple buttons that you are not sure of the name of each
You want to execute the code of these specific buttons via VBA

Are these the only buttons with macros assigned on the sheet?

In this case change how you think about running the code. You are not trying to replicate the human action of clicking the buttons, you are trying to replicate running each buttons code. If so, just loop the shapes on the sheet with macros assigned and run them in that loop. In this fashion you do not need to know each buttons name, nor reference its code directly.

If I am not mistaken there is a buttons collection you can loop. If you are using ActiveX controls I think there is a collection for them as well.

The end result would be as if someone had clicked all the buttons on the sheet, programatically we are not clicking the buttons (but using some method of the button object to execute its code)

**1aaaaaaaaaaaaaaa** · 06-06-2018, 01:38 PM

I see, so the issue is that you are using the clicked button object itself to determine what row to run the code to insert an additional row/floor.

Please Login or Register  to view this content.

If you were to try to call the function directly without clicking on a button object, that line will fail because there is no button object to reference.

What you may be able to do is manually specify the range the row to use when you are calling the function directly, and if it's being called from a button use the button as a reference to determine the row.

We will create an shape object, if the function is called from a button, we will set the shape object to the button caller, if not, we will loop through all shape objects on the worksheet and look for the shape that matches the row we specified when we called the function directly, and set the shape object to that row's button.

I modified your "AV_Schedule_Add_Row" sub to demonstrate.

Please Login or Register  to view this content.

**anorris_TSAV** · 06-06-2018, 01:55 PM

Interesting!

I've never tried setting a shape to the button, and then cycling through each shape on the sheet to find the one that most closely fits the row the button should be in.

The Add Row function currently is working as-intended.

The Add Floor is really the one that I wish to simulate MANY times to test the naming portion. (I apologize for not mentioning that part specifically once posting the link)

It looks like that would could be called using

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

and with that change, I tried testing

Please Login or Register  to view this content.

but I get the error that "Compile error: sub or function not defined"

**Arkadi** · 06-06-2018, 01:56 PM

So here is some code, more that you bargained for I bet... that will actually calculate the position of the button on the screen and perform a mouse click. In my case the Sheet is just sheet1, the button is "Button 1" and has the macro "Test" assigned. Ammend those details as needed.

Please Login or Register  to view this content.

I can't claim credit for all of this, I had some code snippets saved in my library and just modified it a bit to suit your situation.

The Screen positioning code came from mrexcel while moving and clicking the mouse is credited to Ryan Wells over at wellsr.com

**anorris_TSAV** · 06-06-2018, 01:59 PM

Oh wow, that's like literally telling your mouse to perform a click at that location!

I'm going to have to play around with that setup.

**1aaaaaaaaaaaaaaa** · 06-06-2018, 02:00 PM

Hi,

This should work for you to add a floor, I adapted in the same way-

Please Login or Register  to view this content.

**anorris_TSAV** · 06-06-2018, 02:09 PM

Great!

The formatting is strange, but it serves the purpose I was looking to test!

It's strange (to me) that when actually clicking the button now with that macro edit, that it just does nothing.

but running the test case adds in the newly named cells!

This will work perfectly for what I was trying to do.

Thank you very much!

**1aaaaaaaaaaaaaaa** · 06-06-2018, 02:09 PM

Attaching modified workbook

**1aaaaaaaaaaaaaaa** · 06-06-2018, 02:11 PM

I made a typo in the lines, make sure it reads like below for both subs.

Please Login or Register  to view this content.

**1aaaaaaaaaaaaaaa** · 06-06-2018, 02:12 PM

Originally Posted by anorris_TSAV

Great!

The formatting is strange, but it serves the purpose I was looking to test!

It's strange (to me) that when actually clicking the button now with that macro edit, that it just does nothing.

but running the test case adds in the newly named cells!

This will work perfectly for what I was trying to do.

Thank you very much!

You're welcome, if you're issue is resolved, please mark this thread as solved, thank you

**anorris_TSAV** · 06-06-2018, 02:16 PM

That did the trick!

not sure why

Please Login or Register  to view this content.

(even tried with changing the 1 to 13)

Didn't work. It that based on a different method of assigning code to a button? Instead of assigning a macro to a button?

**1aaaaaaaaaaaaaaa** · 06-06-2018, 02:26 PM

Originally Posted by anorris_TSAV

That did the trick!

not sure why

Please Login or Register  to view this content.

(even tried with changing the 1 to 13)

Didn't work. It that based on a different method of assigning code to a button? Instead of assigning a macro to a button?

Yes, sorry for the confusion, that was just a generic example of how one might call a button's code directly without clicking it, not directly tailored to your example or use case.

The function name is literally "CommandButton1_Click()" and the command button points to that function name, the function name could be "HelloWorld123()" and the button could point to it and still run, so the function name is not what makes the code run, it's the association between the button and the code, the button calls the function by name, and the function can use properties of the calling button when running (if it's called by the button).

But, if you're trying to run the code directly without calling it from a button object, you no longer have access to any properties of the object, so you no longer can tell the code what row to look at in your specific case, so we would need some way to pass those parameters manually when the button object is not available.

**anorris_TSAV** · 06-06-2018, 02:31 PM

Ahh, I see.

I had encountered that form of command as a possible solution in a few other forums, but could never get it to do more than error out.

How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Re: How to make VBA click on a button?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] make button single click to avoid user double click

[SOLVED] click on a color button and after button click it will color entire row of cell you click

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions