PDA

View Full Version : [SOLVED] ActiveX button VBA references contents of button sheet even after activating new one



tbaker818
12-16-2014, 02:38 PM
I have some very simple code that works with a regular macro button but not an ActiveX button. I just activate a different sheet and select range A1. Selecting range A1 produces "Select method of Range class failed" error. I found through some debugging that it's looking at the A1 on the sheet where the button resides, not the one that has just been activated. This seems to be a real problem. How can I work around this? :banghead: Again, identical code is fine with standard macro button. Code:


Private Sub CheckInCycle_Click()
Sheet25.Activate
Range("A1").Select
End Sub

Kenneth Hobs
12-16-2014, 03:25 PM
Reference a routine in a Module when using that method in a sheet's Private sub. Note that activating and selecting are seldom needed to achieve most goals.

tbaker818
12-16-2014, 03:42 PM
Aha, forgot about that when programming in sheet modules. Thanks!

Aflatoon
12-18-2014, 02:15 AM
In a worksheet module, any unqualified range reference refers to that sheet, not the active one, so you just need to specify:

Private Sub CheckInCycle_Click() With Sheet25
.Activate
.Range("A1").Select
End With
End Sub