Open Cell Link(Bring to Front) with CommandButton on Userform

[bdb1974]

Hi all,

I've got a generated link which is placed into a sheet cell of my WB.
I need to also provide access to the file with a link on a userform through
a command button or a generated hyperlink (Label),if that's possible.

I am able to open with the following code using a command button.
However, thus far have not be able to figure out how to bring the openned excel spreadsheet forward / to the front while the userform is open.

Any help is greatly appreciated.

Private Sub CommandButton7_Click()
Dim MAS As Worksheet
Dim Excel As Excel.Application
Dim WB As Workbook
Dim NumBooks As Integer, N As Integer
Dim ThisBook As Integer, FirstBk As Integer

Set MAS = ThisWorkbook.Sheets("MASTER")
Set Excel = New Excel.Application


 On Error GoTo 1
 Workbooks.Open FileName:=GetAddress(MAS.Cells(4, "E"))




'Application.ScreenUpdating = False
Application.DisplayAlerts = False
NumBooks = Workbooks.Count
If Workbooks(1).Name = ThisWorkbook.Name Then
FirstBk = 2
Else
FirstBk = 1
End If

For N = 1 To NumBooks
If Workbooks(N).Name = ActiveWorkbook.Name Then ThisBook = N
Next

If ThisBook < NumBooks Then
' move to the next books
Workbooks(ThisBook + 1).Activate
End If

If ThisBook = NumBooks And NumBooks > FirstBk Then
' move back to first book
Workbooks(FirstBk).Activate
Else
MsgBox ActiveWorkbook.Name
'ActiveWorkbook.Activate
End If
Exit Sub
1:           MsgBox Err.Description
End Sub

Thanks,

BDB

[davegugg]

I think this is happening because userforms are by default modal. When you open the workbook it should be brought to the front, so you shouldn't need any code to bring it to the front. Go to the userform's properties; there is a property called ShowModal. If you set that to false, your workbook should still be accessible and the newly opened workbook will go to the top.

[bdb1974]

Yes, this helped some.

The openned wb is now visible over the main wb, but I still can't bring it up over
the userform. Can I just use some code to open it into a new excel app.

Private Sub CommandButton7_Click()
Dim MAS As Worksheet
Set MAS = ThisWorkbook.Sheets("MASTER")
Dim Excel As Excel.Application
Dim WB As Workbook

Set Excel = New Excel.Application ' would like to use this ...



Workbooks.Open FileName:=GetAddress(MAS.Cells(4, "E"))

Any help is appreciated.

THanks,

BDB

[davegugg]

Yes, you can do that. I wouldn't use Excel as a variable name. Instead, I alway use xls. After you create the new Excel instance, just make it visible:

Dim xls As Excel.Application

Set xls = New Excel.Application

xls.Visible = True

Then you will need to put xls. in front of any commands you want to run in the new instance of excel.

[bdb1974]

Hi all,

I found some code here at:
http://www.ozgrid.com/forum/showthread.php?t=61588

Ref: "Force Workbooks Open New Process"

I simply stuck the code into the userform and put in an operation to run the
code in succession. This brought it forward just as I needed.

Thanks Dave for your assistance.


BDB