Input value for specific workbook variable

[Eric Penfold]

Need help to use Input value to reference open workbook? When i try it shows nothing in the Case command tried If Statement but it`s the same result??

Public Sub Depot_Name()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim Result As Variant

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
  
    Result = Application.InputBox("Type Depot Name Alton\Cov\Basildon", "What is your Current Open Workbook", "Type Depot Name Here")
  
    Select Case Result

        Case Is = "Alton"
        Set wb = Workbooks("S:\PURCHASING\Stock Control\Alton\2023\2023 Alton Back OrderT.xlsm")
          
        Case Is = "Cov"
            Set wb = Workbooks("S:\PURCHASING\Stock Control\Coventry\2023\2023 Coventry BackOrder.xlsm")
          
        Case Is = "Basildon"
            Set wb = Workbooks("S:\PURCHASING\Stock Control\Basildon\2023\2023 Basildon BackOrder.xlsm")
          
    End Select
  
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

 

[LazyBug]

Remove or comment your

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With

block (In this code specifically, I do not see the reasonability of using it) or move it after the line

End Select

plus amend your data type of Result to string:

Result = Application.InputBox("Type Depot Name Alton\Cov\Basildon", "What is your Current Open Workbook", "Type Depot Name Here",,,,,2)

 

[Eric Penfold]

I`ve taken the application blocks away but the wb still say`s out of context?

 

[dmt32]

Hi,
see if this update to your code does what you want

Public Sub Depot_Name()
    
    Dim wb           As Workbook
    Dim Result      As Variant
    Dim Depot()     As String, strFileName As String
    
    Const strFolderName As String = "S:\PURCHASING\Stock Control\"
    
    Depot = Split("Alton,Coventry,Basildon", ",")
    
    Do
        Result = InputBox("Enter Depot Number" & Chr(10) & Chr(10) & _
                 "1 - " & Depot(0) & Chr(10) & _
                 "2 - " & Depot(1) & Chr(10) & _
                 "3 - " & Depot(2) & Chr(10) & Chr(10) & _
                 "To Select your Current Open Workbook", "Select Depot")
        'cancel pressed
        If StrPtr(Result) = 0 Then Exit Sub
    Loop Until Val(Result) > 0 And Val(Result) < 4
    
    strFileName = "2023 " & Depot(Result - 1) & " BackOrder.xlsm"
    
    For Each wb In Workbooks
        If wb.Name = strFileName Then wb.Activate: Exit Sub
    Next
    
    
    If MsgBox(strFileName & Chr(10) & "Workbook Not Found Do You Want To Open It?", 36, "Open Workbook") = vbNo Then Exit Sub
    Set wb = Workbooks.Open(strFolderName & Depot(Result - 1) & "\2023\" & strFileName, 0, False)
    
End Sub

Dave

 

[Eric Penfold]

Remove or comment your

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With

block (In this code specifically, I do not see the reasonability of using it) or move it after the line

End Select

plus amend your data type of Result to string:

Result = Application.InputBox("Type Depot Name Alton\Cov\Basildon", "What is your Current Open Workbook", "Type Depot Name Here",,,,,2)

I`ve done the above but it still say`s wb as Out of context

 

[Eric Penfold]

Hi,
see if this update to your code does what you want

Public Sub Depot_Name()
   
    Dim wb           As Workbook
    Dim Result      As Variant
    Dim Depot()     As String, strFileName As String
   
    Const strFolderName As String = "S:\PURCHASING\Stock Control\"
   
    Depot = Split("Alton,Coventry,Basildon", ",")
   
    Do
        Result = InputBox("Enter Depot Number" & Chr(10) & Chr(10) & _
                 "1 - " & Depot(0) & Chr(10) & _
                 "2 - " & Depot(1) & Chr(10) & _
                 "3 - " & Depot(2) & Chr(10) & Chr(10) & _
                 "To Select your Current Open Workbook", "Select Depot")
        'cancel pressed
        If StrPtr(Result) = 0 Then Exit Sub
    Loop Until Val(Result) > 0 And Val(Result) < 4
   
    strFileName = "2023 " & Depot(Result - 1) & " BackOrder.xlsm"
   
    For Each wb In Workbooks
        If wb.Name = strFileName Then wb.Activate: Exit Sub
    Next
   
   
    If MsgBox(strFileName & Chr(10) & "Workbook Not Found Do You Want To Open It?", 36, "Open Workbook") = vbNo Then Exit Sub
    Set wb = Workbooks.Open(strFolderName & Depot(Result - 1) & "\2023\" & strFileName, 0, False)
   
End Sub

Dave

Thanks this is great but I need to capture the file that is already open name? Moment it says wb out of context?

 

[dmt32]

This part of the code

For Each wb In Workbooks
        If wb.Name = strFileName Then wb.Activate: Exit Sub
    Next

checks eack open workbook & if name matches the strFileName variable activates it otherwise you see msgbox asking if want to open then file

did you see the Msgbox prompt?

Dave

 

[Georgiboy]

If the workbook is open then you need to drop the file path:
so instead of:

Set wb = Workbooks("S:\PURCHASING\Stock Control\Coventry\2023\2023 Coventry BackOrder.xlsm")

You need:

Set wb = Workbooks("2023 Coventry BackOrder.xlsm")

 

[Eric Penfold]

I`ve used your code.
But i can`t seem to get the wb to return the already open workbook name?
Yes i used your prompt but it didn`t return the wb name?

This part of the code

For Each wb In Workbooks
        If wb.Name = strFileName Then wb.Activate: Exit Sub
    Next

checks eack open workbook & if name matches the strFileName variable activates it otherwise you see msgbox asking if want to open then file

did you see the Msgbox prompt?

Dave

I

 

[dmt32]

sorry not been fully following what you are trying to do but try this modification to part of of the code

For Each wb In Workbooks
        If wb.Name = strFileName Then Set wb = Workbooks(strFileName): Exit Sub
    Next

and see if this helps

Dave