why do i have to click my 2nd command button twice

[rjudge]

I have a userform with 2 command buttons. When I click the first one it
goes to another workbook and brings back data. When I click the second
one it does nothing but when I click it again it works. Why is it
taking 2 clicks for it to work...Robbie

Private Sub get1262_Click()
ChDir "P:\"
Workbooks.Open(Filename:="P:\1262goaling.xls").RunAutoMacros
Which:= _
xlAutoOpen
End Sub

Private Sub get1264_Click()
ChDir "P:\"
Workbooks.Open(Filename:="P:\1264goaling.xls").RunAutoMacros
Which:= _
xlAutoOpen
End Sub

 

[Scoops]

Hi rjudge

Set the button's TakeFocusOnClick property to False or include a line
in the first button's code:

get1264.SetFocus

Regards

Steve

 

[rjudge]

Steve
Thanks for the reply. Tried this but still had the same problem. Is
there a way to 'refresh' the form before I press the second command
button?

Robbie

 

[Udo]

Hi rjudge;

when you run the first code, it opens another workbook and gives the
focus to that workbook. I suggest to insert a line of code to come back
to the original workbook like
ThisWorkbook.Worksheets("Sheet1").Activate

Hope that helps
Udo

 

[rjudge]

Thanks Udo for your reply but unfortunately it didn't work. I can pres
another command button that closes the first file I open and it work
with 1 click but when I go to open the 2nd file using the comman
button it still requires 2 clicks.

Robbi

 

[Scoops]

Hi Robbie

Just tried this with a scratch UserForm and two CommandButtons, each
opening a different workbook and each with the TakeFocusOnClick set to
False.

I can click both buttons once and they fire the open command (or
activate the appropriate workbook on subsequent single clicks).

Is there anything in the opening workbook macros that divert the Focus
elsewhere?

Regards

Steve

 

[rjudge]

Steve
Before I open the form I copy data from a table on an internal work we
site. When I hit the first command button the data is pasted into
workbook where it is filtered and some other work is done on it. I the
repeat the procedure copying more data. To do this I go out of the for
but don't close it. I then return to the form and hit the secon
command button and it depresses but does nothing until I click i
again. I can get it working if I don't use a form by just having th
buttons on a excel workheet but I think the form looks better.

Robbi

 

[Scoops]

Hi Robbie

Not sure what's happening here; if a UserForm is open, doesn't that
prevent you from interacting with a workbook until you close it?
(Sorry if I'm missing something obvious, it's after 1am now and I'm not
totally awake.)

It would also seem that your second button does have the focus if it
depresses - hmm.

If you're worried about presentation, perhaps you could put a couple of
custom buttons on a toolbar and assign the macros to them: with the
workbook containing the macros open - Tools>Customize, Commands tab,
Categories: Macros, drag the Custom Button to a toolbar, right-click it
and Assign Macro.

Or maybe it just needs to rest for the weekend, take in some Australian
F1, play some Oblivion...

Regards

Steve

 

[rjudge]

Steve
Got sorted. The second command button when pressed only opened the
workbook but didn't open the macros even though I have an Auto_Open
command at the beginning so I just opened the second workbook at the
end of the macro called by the first command button and when the second
command button is pressed everything works. Probably not the most
ingenious programming but it works.

Robbie

 

[Scoops]

Well done Robbie, glad you got it figured out.

Regards

Steve