Puase macro to allow user to open/activate a WB, continue

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
I can't quite figure out code needed to pause a proceedure, allow user to activate a sheet in another workbook,
may involve opening it first, then let proceedure continue.

Best would be a message prompt:
"Please open/select as active your personal workbook now."
"Select OK once you've done this."

Selecting 'OK' button would allow the proceedure to continue.
Cancel would goto error handler I can write.

MsgBox doesn't allow other task until after OK is selected... :confused:
Thanks, TTom
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Couldn't you use GetOpenFilename?
Code:
Dim wbPers As Workbook
Dim varFile As Variant
    ' some code
 
    varFile = Application.GetOpenFilename
 
    If TypeName(varFile) = "Boolean" Then
        MsgBox "No file selected. Code cancelled."
        Exit Sub
    Else
        Set wbPers = Workbooks.Open(varFile)
    End If
    
    ' more code here
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
Thanks Norie,
It may be the case that the workbook is already open and only needs to be selected.
Running GetOpenFileName code first creates an error if this is the case.
How would I give a choice to select vs. open?
I should have an error handler if they try to open instead of selecting the already open workbook.

TTom :wink:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Tom

Try a board search for code that checks to see if a workbook is open, there's plenty out there.:)

By the way you mention selecting the workbook?

What exactly are you doing?

Generally there's no need to select workbooks/sheets etc to work with them.
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518

ADVERTISEMENT

I should say activate workbook, vs. select?
I hdo ave code to see if workbook is open provided the <u>path and name are known</u>.
I don't want the user to have to enter this, and it will change regularly.

What am I doing? Yes, that might help...
I will be transfering data from active workbook to a workbook determined by user. It will be a copy/paste function in marco.
My user may or may not have the workbook open they will have macro transfer data to.

I planned to first use: OriginalName = ThisWorkbook.Name
After the user has either "Activated" their workbook by opening it or by selecting it as the active workbook, I would use the code:
TempName =ActiveWorkbook.Name

I can now let the macro switch between the two workbooks using their assigned names,
Workbooks(OrginalName).Activate or Workbooks(TempName).Activate,
and do whatever process needed, in this case it will be some copy/paste controled by the macro.
It won't matter if other unrelated workbooks are open at the same time.

Further comments welcome :)

TTom
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Tom

There is no need to activate or select for this type of thing.

In the code I posted a reference, wbPers, is created to the opened workbook.

This can then be used in subsequent code whenever you want to refer to that workbook.
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518

ADVERTISEMENT

I could use a query:

Dim Msg, Style, Title, Response
Msg = " Is your workbook already open?"
Style = vbYesNo + vbQuestion+ vbDefaultButton1
Title = "Transfer data query?"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes
'code to allow user to activate the open workbook, then continue macro
End If

If Response = vbNo Then ' User chose No
'Run GetOpenFileName code, then continue macro
End If

'more code
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
Was typing my additional note when your response came in.
Looking at your response. TT
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Tom

As far as I can see you do not need to activate/select anything.

Yes you could use code to get user input to see if a workbook is open, but why not combine GetOpenFileName with code that checks to see if the workbook is open?
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
Okay, I think I understand your point.
Where in the original code you posted would I place the check to see if file is already open?

Also, I thought if user <u>knew the workbook was already open</u> it would be easier for user to simply activate it rather than browsing through an open proceedure? Some of the workbooks are buried deep in other directory paths than current one.

I'm obviously not quite as advanced as you, thus my need for a bit of hand holding assistance which is greatly appreciated!! :biggrin: Bear with me... :wink:
 

Forum statistics

Threads
1,136,592
Messages
5,676,690
Members
419,644
Latest member
KeelsM

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