Activate Workbook

radlernyc

Board Regular
Joined
Jun 9, 2005
Messages
69
Have code that drops info from a Userform into worksheet upon CommanButton Click.

Code:
worksheets("data").Range("P" & updaterow) = "X"


The macro crashes if a user has multiple worksheets open and accidently clicks the Comman Button with the wrong workbook active.

Anyone have code to activate workbook that contains a specific worksheet ie. worksheets("data")

Thanks for the help
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why not just reference the appropriate workbook as well as the worksheet?
Code:
Workbooks("MyWorkbook.xls").
Worksheets("data").Range("P" & updaterow) = "X"
 

radlernyc

Board Regular
Joined
Jun 9, 2005
Messages
69
the filename will be changed by the user. Can you dynamically code so that the filename change also updates in the code?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Yes it is possible.

But I think we would need to see more of the code, and some more explanation of what you are actually doing.:)
 

radlernyc

Board Regular
Joined
Jun 9, 2005
Messages
69
Not sure what other infor you need... Just having trouble figuring out how to activate a workbook among all currently OPEN workbooks containing a worksheet with a specific name (i.e. worksheets("data"))

or (prob much easier)

code that returns an msgbox if activeworkbook does not contain worksheet("data")... either will do.

I would post the code, but its lengthy as it is dropping data from over 100 textboxes to a worksheet.

Thanks for your time and help
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

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
Top