Using VBA range commands with ActiveX buttons in other sheets

Question

while building up a user Interface I made use of ActiveX buttons in VBA2010. I wrote the Macros first, checked them, then copied them into the ActiceX button code.

The macro then crashes at a range command of another sheet. To test it I separated the commands:

Sheets("Data").Visible = xlSheetVisible 
Sheets("Data").Select                   
Columns("O:O").Select         ' The code crashes here

As a result I get a 1004 runtime error. I found a similar topic which seems to be unsolved aswell.

Unable to Execute Macro With ActiveX Controls (Excel VBA)

Help is well appreciated. Thanks in advance!

score 0 · Accepted Answer · answered Apr 07 '16 at 07:49

0

Select is not recommended.Modify your code to

 Dim ws As Worksheet
 Set ws = Sheets("Data")
 ws.Visible = xlSheetVisible
 ws.Select
 With ws
 .Columns("O:O").Select
 End With

answered Apr 07 '16 at 07:49

Neelesh

138
1
10

Click on the check mark if that answers your question. – Neelesh Apr 07 '16 at 08:06
Will do so. Thanks :) – yfro Apr 07 '16 at 08:16

Using VBA range commands with ActiveX buttons in other sheets

1 Answers1