Workbook Activate Issue

[AndrewGKenneth]

Dear all,

Thanks in advance for your help. I am having a slight issue with my code below. What I am hoping to achieve with this code is that the user enters their details into a userform (Employee Number and Password) and then they are taken to their own workbook and the current workbook is closed. This is working fine, apart from when the user has another document open on their computer. The problem I am having is that the file (in this case "10018 - Assembly") is not showing as the activeworkbook if the user has another file open on their computer. Instead, whatever file they may have open is showing as the activeworkbook despite my code. . I really need the (10018 - Assembly) file to be activated. Any suggestions on how to fix this?

Application.ScreenUpdating = True

If TextBox_EmployeeNumber.Value = "10018" And Textbox_Password.Value = "190453" Then

Workbooks.Open Filename:="X:\Drawing - Pricing\Absence Data\10018 - Assembly.xlsm"

Workbooks("10018 - Assembly.xlsm").Activate

ThisWorkbook.Close SaveChanges:=True

Unload Me

End If

Application.ScreenUpdating = False

End Sub

 

[dougmarkham]

Hi Andrew,

This link shows a method: https://social.msdn.microsoft.com/Forums/vActivate-an-already-open-excel-workbook?forum=vsto

Here is the VBA you might try: an example to check if the workbook is open or not. If not open it open the workbook:

Sub CheckIfOpen()

Dim wbName As String = "Test.xlsx"
Dim PathName As String = "c:\Test\" & wbName
Dim wbBook As Excel.Workbook

For Each wbBook In xlApp.Workbooks
If wbBook.Name = wbName Then
wbBook.Activate()

Exit Sub
End If

Next

Dim xl_wbBook As Excel.Workbook = xlApp.Workbooks.Open(PathName) 

End Sub

 

[AndrewGKenneth]

Thanks Doug, I will give that a go ! Much appreciated

 

[AndrewGKenneth]

Hi again Doug,

I think this is the right solution to my problem, but cannot seem to apply it to my code (as still a beginner in VBA). Would you be able to help me with this? I am struggling to get it to work. I think it may be something to do with the xlapp.workbooks element. Thank you !

 

[dougmarkham]

Hi again Doug,

I think this is the right solution to my problem, but cannot seem to apply it to my code (as still a beginner in VBA). Would you be able to help me with this? I am struggling to get it to work. I think it may be something to do with the xlapp.workbooks element. Thank you !

Hi Andrew,

If you post your code along with any particular information on tables you think will explain the situation, then we'll all be able to see what might be going on.
Also, what excel version do you have?
Are your excel programs running on a virtual network e.g., Citrix network or any other form of virtual server?

Kind regards,

Doug

 

[AndrewGKenneth]

Hi Doug,

Sorry for the late reply, I wasn't in the office over the weekend but have managed to resolve the issue this morning . Thanks again for all your help much appreciated!