Command buttons not working when on different workbook

Alan_CT06

New Member
Joined
Nov 6, 2023
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
I have my VBA code and a ActiveX command button which runs the code in one workbook. I need the code to run against data in different workbooks which will have different names.

The code works fine against data in a different workbook if I run it from the VBA application window.

The code can also run when I start it using a command button if I copy the data onto the same worksheet where the command button is

The problem seems to be that the code doesn't start the first time I click on the command button. All that first click does it make the worksheet where the command button is the current worksheet. When I then click the second time, the code runs, but it fails because if is looking for the data on the worksheet where the button is and not in the workbook where the data is.

I tried changing the button property "Take Focus on Click" to False, but it makes no difference.

I've used command buttons before without problem but am at a new company and perhaps there is a different setting on this version of Excel or at this company. We use Microsoft 365 here.

Thanks for any help.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I don't think the command button itself is the issue. Can you share your code here? (I recommend marking code with code tags)
 
Upvote 0
Thank you for responding. The code is very long but even if I use the most simple code, I can't make a command button in one work book execute the code the first time I click the command button.

For example in workbook named "Workbook A" in Module 1 I created this sub.

Sub Test1()
Range("A5").Value = 6
End Sub

Then in Workbook A, I created a Command Button, and created the code for the button,

Private Sub CommandButton1_Click()
Test1
End Sub


Now I would like to use my sub Test1 to enter "6" in Cell B5 in some other workbook that I happen to be working on at the time.

I make that other workbook active, then click on the Command Button on Workbook A to kick off the Sub Test1, and nothing happens. The only thing that seems to happen on that first click is that Workbook A becomes the active workbook.

Then if I click the button again, the sub run, and enters "6" in cell A5 in Workbook A. And this isn't what I want because I want to enter 6 in the other workbook I am working on.

Is this clear?

To say another way, if I am working in another workbook, and I click the command button located on Workbook A, want that first click to kick off my sub, not make WorkbookA the active workbook.
 
Upvote 0
I have found an answer to my question. It has been explained to me that for a command button to work, the workbook holding the command button has to be the active workbook.

And if I want to code to run against another file, that has to be coded into the sub to switch the active workbook status to that other file.
 
Upvote 0
Solution

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top