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

 

[Eric Penfold]

Ok that`s great and works but how can i use the the workbook return in a different module?

 

[dmt32]

Ok that`s great and works but how can i use the the workbook return in a different module?

a few ways can do that one would be to pass an argument to the code & return its value ByRef

updated code

Sub Depot_Name(ByRef 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 Set wb = Workbooks(strFileName): 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

to call it

Sub mysub()
    Dim wb As Workbook
    
    Depot_Name wb
    
    If Not wb Is Nothing Then
    
    'rest of your code
    
    End If
    
End Sub

other ways would be to make code a Function that returns the workbook object or declare the wb object variable in a standard module as Public but personally, I would avoid doing this

Dave

 

[Eric Penfold]

Still not working i will send the whole code to you. Say`s wb out of context?

Sub VLookup()

    Dim SrcReD As Workbook, wb As Workbook, Alton As Workbook, Cov As Workbook, Basildon As Workbook
    Dim ws     As Worksheet, SrcRed_ws As Worksheet
    Dim wsLRow As Long, wsLCol As Long, col_wsRed As Long
    Dim i      As Integer
    Dim LRow   As Long, LCol As Long, col_wsTyp As Long
    Dim FileToOpen As Variant, arrDes_Rng As Variant
    Dim SrcRed_Rng As Range, Cell As Range, Des_Rng As Range
    Dim BlCell As Boolean
    Dim mydate As String
    Dim Result As Variant
    BlCell = False
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Depot_Name wb
    If Not wb Is Nothing Then
        mydate = Format(Date, "mmm")
        Set ws = wb.Worksheets(mydate)
        
        FileToOpen = ("S:\PURCHASING\Stock Control\Reports\Back Order Admin\Back Order Release Date.xlsx")
        Workbooks.Open FileToOpen
        
        Set SrcReD = Workbooks("Back Order Release Date.xlsx")
        
        Set SrcRed_ws = SrcReD.Sheets("Sheet1")
        
        LRow = SrcRed_ws.Cells(Rows.Count, 1).End(xlUp).Row
        Set SrcRed_Rng = SrcRed_ws.Range("A2:C" & LRow)
        
        wsLRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
        wsLCol = 16
        Set Des_Rng = ws.Range(ws.Cells(2, "A"), ws.Cells(wsLRow, wsLCol))
        
        arrDes_Rng = Des_Rng.Value
        arrDes_Rng = Application.Trim(arrDes_Rng)
        
        col_wsRed = 3
        
        Des_Rng.Columns(col_wsRed).Value = Application.Index(arrDes_Rng, 0, col_wsRed)
        
        Call Delete_NSI
        
        If ws.Name <> "Summary" And ws.Name <> "Trend" And ws.Name <> "Supplier BO" And ws.Name <> "Diff Depot" _
            And ws.Name <> "BO Trend WO" And ws.Name <> "BO Trend WO 2" And ws.Name <> "Different Depot" Then
            
            With ws
                
                .Range("M2:M" & wsLRow).ClearContents
                
                For i = 2 To wsLRow
                    Set Cell = .Range("M" & i).Value
                    .Range("M2" & i).Clear
                    If IsEmpty(.Cells(i, 13).Value) Then
                        .Range("M" & i).Value = .Application.IfError(.Application _
                                    .VLookup(Des_Rng.Cells(i - 1, col_wsTyp), SrcRed_Rng, 2, 0), "")
                        .Range("M2:M" & wsLRow).HorizontalAlignment = xlCenter
                    End If
                Next i
                
            End With
            
            SrcReD.Close
            
            Call Number_To_Text_Macro
            
        End If
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub
Sub Depot_Name(ByRef 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")
        
        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 Set wb = Workbooks(strFileName): Exit Sub
    Next
    
End Sub

 

[dmt32]

you reported code worked?

Cannot test all your code but I got as far as msgbox I inserted & it reported selected filename which suggests updated code is doing what you wanted.

Sub VLookup()

    Dim SrcReD As Workbook, wb As Workbook, Alton As Workbook, Cov As Workbook, Basildon As Workbook
    
    
    Depot_Name wb
    
    If Not wb Is Nothing Then
        mydate = Format(Date, "mmm")
        Set ws = wb.Worksheets(mydate)
        
        MsgBox wb.Name & Chr(10) & ws.Name

'rest of code

suggest that you step through your code & share with Forum line of code that errors for you

Dave

 

[Eric Penfold]

The Depot_Name wb line say`s "Nothing" rather than the wb name

Depot_Name wb
    
    If Not wb Is Nothing Then

 

[dmt32]

if wb object variable is Nothing at that point this indicates that the filename has not been found
Either the strfilename construct is not correct for the file you have open or it is not open?

strFileName = "2023 " & Depot(Result - 1) & " BackOrder.xlsm"

Check that the variable name fully matches the files you are selecting

I see that you removed the option to open then file if this occurs - try putting it back & if message box appears see if selecting Yes to the prompt resolves the issue

Dave

 

[Eric Penfold]

The wb matches in the Depot Name code but when is goes back to the code VLookup it say`s wb = "Nothing"?

 

[Georgiboy]

Going back to your original code, if all three of the below files are open already:
2023 Alton Back OrderT.xlsm
2023 Coventry BackOrder.xlsm
2023 Basildon BackOrder.xlsm

The variables for wb & ws would need to be outside of the subs for this method.

If you want to pass the wb & ws variable from one sub to another, you can use something like the below (sticking closely to your original code), try running the 'test' sub:

Option Explicit

Dim wb As Workbook
Dim ws As Worksheet

Sub Depot_Name()
    Dim Result As Variant

    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("2023 Alton Back OrderT.xlsm")
            Set ws = wb.Sheets(Format(Date, "mmm"))
        Case Is = "Cov"
            Set wb = Workbooks("2023 Coventry BackOrder.xlsm")
            Set ws = wb.Sheets(Format(Date, "mmm"))
        Case Is = "Basildon"
            Set wb = Workbooks("2023 Basildon BackOrder.xlsm")
            Set ws = wb.Sheets(Format(Date, "mmm"))
    End Select
    
End Sub

Sub test()
    Depot_Name
    MsgBox wb.Name & vbNewLine & ws.Name
End Sub

 

[dmt32]

I am using code you posted - having created a dummy workbook for Coventry the wb object variable returns the workbook to the calling procedure.

just to understand what is happening - add the line where shown in BOLD in the Depot_Name code & see if it displays

For Each wb In Workbooks
        If wb.Name = strFileName Then Set wb = Workbooks(strFileName): Exit Sub
    Next
  
    MsgBox strFileName & Chr(10) & "File Not Found", 48, "Not Found"

Dave

 

[Eric Penfold]

Thanks all working correct now. Can I use it in a separate module? Or does it need adjustment for that?