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

[TTom]

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...
Thanks, TTom

 

[Norie]

Couldn't you use GetOpenFilename?

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]

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]

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]

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]

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]

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]

Was typing my additional note when your response came in.
Looking at your response. TT

 

[Norie]

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]

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!! Bear with me... :wink: