ComboBox Value to use in VBA formula gets Run-time error 424 Object Required

[Bud Wilkinson]

Hi All,
I have a Userform that sucessfully populates a pair of Source Combo Boxes and a pair of Target Combo Boxes with open Workbook and Worksheet names that I am having trouble using in a VBA formulas.

cboSourceBook, cboSourceSheet, cboTargetBook and cboTargetSheet are the names of the properly populated combo boxes.
The SetSourceWorkbook .... Line generates a Run-time error '424': Object Required error.

Private Sub cmdImport_Click()

cpyRange

End Sub

Sub cpyRange()

Dim SourceSheet As Excel.Worksheet
Dim SourceWorkbook As Excel.Workbook
Dim RangeName As String
Dim TargetWorkbook As Excel.Workbook
Dim TargetSheet As Excel.Worksheet

Application.ScreenUpdating = False
Set SourceWorkbook = cboSourceBook.Value 'Instead of = Workbooks.Open("C:\Users\~User\~Folder\~File.xlsm")
Set SourceSheet = cboSourceSheet.Value
SourceSheet.Range("Ride_Ht_LF").Copy
Set TargetWorkbook = ThisWorkbook
Set TargetSheet = cboTargetSheet.Value
TargetSheet.Range("Ride_Ht_LF").PasteSpecial Paste:=xlPasteValues

'
'

SourceWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
MsgBox ("done")
End Sub

This code works as a macro but I wanted to set the Source and Target Workbooks and Worksheets with the values in the Combo Boxes and not have to use the "Workbooks.Open" and drop the "SourceWorkbook.Close" statements.

Sub cpyRange()

Dim SourceSheet As Excel.Worksheet
Dim SourceWorkbook As Excel.Workbook
Dim RangeName As String
Dim TargetWorkbook As Excel.Workbook
Dim TargetSheet As Excel.Worksheet

Application.ScreenUpdating = False
Set SourceWorkbook = Workbooks.Open("C:\Users\~User\~Folder\~File.xlsm")

SourceWorkbook.Sheets("S1").Range("Ride_Ht_LF").Copy
Set TargetWorkbook = ThisWorkbook
Set TargetSheet = TargetWorkbook.Sheets("Interface")
TargetSheet.Range("Ride_Ht_LF").PasteSpecial Paste:=xlPasteValues

SourceWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
MsgBox ("done")
End Sub

Been trying many options to understand why cboSourceBook.Value isn't an Object but I'm lost.

TIA
Bud

[Tinbendr]

You're not assigning any objects, just values.

Set SourceWorkbook = Workbooks(cboSourceBook.Value)
Set SourceSheet = SourceWorkbook.WorkSheets(cboSourceSheet.Value)

[Bud Wilkinson]

Tinbender,
Thanks for that. It has me halfway home it would seem. Now the trouble is that the cboTargetBook.Value and cboTargetSheet.Value are giving me errors.
When set the same way as the source the Workbook statement gives a Run-time error 9 Subscript Out of Range.
When used as follows it works fine but I would like for the Target to be selected from the Combo Boxes as well.

Sub cpyRange()

Dim SourceSheet As Excel.Worksheet
Dim SourceWorkbook As Excel.Workbook
Dim RangeName As String
Dim TargetWorkbook As Excel.Workbook
Dim TargetSheet As Excel.Worksheet

Application.ScreenUpdating = False
'Set SourceWorkbook = Workbooks.Open("C:\Users\~User\~Folder\~File.xlsm") 'For using specific windows explorer path.

Set SourceWorkbook = Workbooks(cboSourceBook.Value) 'For use with a combo box selection.
Set SourceSheet = SourceWorkbook.Worksheets(cboSourceSheet.Value) 'For use with a combo box selection.
SourceSheet.Range("Ride_Ht_LF").Copy

Set TargetWorkbook = ThisWorkbook
Set TargetSheet = TargetWorkbook.Sheets("Interface")

'Set TargetWorkbook = Workbooks(cboTargetBook.Value) 'Would Rather be able to use the combo box here too.
'Set TargetSheet = TargetWorkbook.Worksheets(cboTargetSheet.Value) 'Would Rather be able to use the combo box here too.

TargetSheet.Range("Ride_Ht_LF").PasteSpecial Paste:=xlPasteValues

Application.ScreenUpdating = True
MsgBox ("done")

ExitForm

End Sub

Thanks,
Bud

[Tinbendr]

Are both workbooks open? The Target and Source?

If so, then you could iterate the workbook collections and add the correct wb names/sheet names to the combobox.

[Bud Wilkinson]

Are both workbooks open? The Target and Source?

If so, then you could iterate the workbook collections and add the correct wb names/sheet names to the combobox.

Tinbendr,
Yes both Workbooks are open.
Sorry to be such a Noob but I'm not sure what is meant by "iterate the workbook collection".
There is a userform that has a frame containing two combo boxes that are Source and another frame with two combo boxes that are Target. the top combo box in each frame is for workbook and the bottom is for choosing the worksheets from the drop down list of the chosen book.

The one thing with the routine of filling the comboboxes for the Target is that it adds some info and looks like "This Book (~Open Workbook File Name.xlsm)" "This Sheet (~Sheet Name)" to denote the book and sheet the form was launched from. It looks like this.

UserForm02.jpg

[Tinbendr]

but I'm not sure what is meant by "iterate the workbook collection".

Excel stores many things in collections. All the workbooks that are open as in a collection. All the shapes on a sheet are a collection. And so on. You can iterate (loop through) these collections looking for information.

But after seeing your snapshot of the frame, I'm even more confused about the flow of the operation. Can you describe how you fill the comboboxes?

Is this code in either of the Target or Source workbooks? If so, then you don't need to put the filename in a dropdown since there is only one selection. You can however, put the sheet names in a combobox. But even this is moot if you copying and pasting from a named range. (I realize that this may be part of an exercise for you, but it's really redundant.)

The tilde (~) is a symbol Office uses to indicate the current open file. It's like a temp file and you don't want to use that as a filename.

[Bud Wilkinson]

Tinbender,
Thanks for the help so far, Sorry to take so long to get back to you......Life.....
Below is the code that populates the Target and Source combo boxes. It was copied and adapted from a different form so I am not positive how it works. I am wondering if the "This Sheet" statement in the target selections could be causing the problem. I realize the using the combo box for the target selections is redundant but there may be incidents where I wish to import the same named values to other open books/sheets at the same time before the mind slips and forgets to do it.
I have probably used inappropriately the tilde as a space saver in examples and not in the actual code.

Option Explicit

    Dim strNameArray() As String
    Dim strTargetBook As String
    Dim strTargetSheet As String
    Dim strSourceBook As String
    Dim strSourceSheet As String
    
    Dim objTargetSheet As Worksheet
    Dim objTargetBook As Workbook
    Dim objSourceSheet As Worksheet
    Dim objSourceBook As Workbook
    
    Dim strBooksOpened() As String
    
    Const strFormVersion As String = "1.0"
    Const strLastModified As String = "Jan 08 2015"


Private Sub UserForm_Activate()

    Debug.Print "Initializing form " & Me.name & "(v" & strFormVersion & ")..."
    
    lblVersion.Caption = lblVersion.Caption & " " & strFormVersion
    lblVersion.ControlTipText = lblVersion.ControlTipText & " " & strLastModified

        ' Enable all buttons from the start
    cmdBrowse.Enabled = True
    cmdImport.Enabled = True
    cmdExit.Enabled = True
        
    LoadAllBooks            ' load all open workbooks into pulldown
    
    ' set target book to active book
    cboTargetBook.Value = "This Book (" & ActiveWorkbook.name & ")"
    Set objTargetBook = ActiveWorkbook 'Workbooks(cboTargetBook.Value)
    
    LoadAllSheets "Target"
    
    ' set target sheet to active sheet
    On Error Resume Next
    cboTargetSheet.Value = "This Sheet (" & ActiveSheet.name & ")"
    Set objTargetSheet = ActiveSheet 'Worksheets(cboTargetSheet.Value)
End Sub

Private Sub LoadAllSheets(strSheetType As String)
    
    Dim cboSheet As ComboBox
    Dim cboBook As ComboBox
    Dim wbBook As Workbook
    Dim SH As Worksheet
    
    Select Case strSheetType
        Case "Target"
            Set cboSheet = cboTargetSheet
            Set cboBook = cboTargetBook
            Set wbBook = objTargetBook
        Case "Source"
            Set cboSheet = cboSourceSheet
            Set cboBook = cboSourceBook
            Set wbBook = objSourceBook
        Case Else
        
    End Select
    
    ' load the source sheets into the poulldown
    
    Debug.Print "LoadAllSheets: Load the source sheets into the poulldown (" & strSheetType & ")"
    
    Debug.Print "Clearing the sheets pull down list..."
    cboSheet.Clear
    
    ' step through each sheet in the workbook
    For Each SH In wbBook.Worksheets
        Debug.Print " " & wbBook.name & " > " & ActiveWorkbook.name
        Debug.Print "Examining " & SH.name
        'If (cboBook.Value = ThisWorkbook.Name And SH.Name = ActiveSheet.Name) Or SH.Visible = xlSheetHidden Then
            ' if the sheet is the same as the target sheet, do nothing
        If SH.Visible = xlSheetHidden Then
            ' if the sheet is hidden, do nothing
        Else
            Debug.Print " " & SH.name & " > " & ActiveSheet.name
            If wbBook.name = ActiveWorkbook.name And SH.name = ActiveSheet.name Then
                cboSheet.AddItem "This Sheet (" & SH.name & ")"
            Else
                cboSheet.AddItem SH.name  ' add it to the pull down
            End If
        End If
    Next
    
End Sub


Private Sub LoadAllBooks()

    ' load open workbooks into pulldown
    
    Dim WB As Workbook
    Dim idx As Integer
    Dim intWindowCnt As Integer
    
    Debug.Print "LoadAllBooks: Load open workbooks into pulldown"
    
    Debug.Print "Clearing the workbook pull down list..."
    cboSourceBook.Clear
    cboTargetBook.Clear
    
    ' step through each workbook that is currently loaded
    ' if it is not hidden then add it to the pull down
    For Each WB In Workbooks
        intWindowCnt = WB.Windows.Count
        For idx = 1 To intWindowCnt
            If WB.Windows(idx).Visible = True Then  ' if a window is visible, add the workbook name to the pulldown
                If WB.name = ActiveWorkbook.name Then
                    cboSourceBook.AddItem "This Book (" & WB.name & ")"
                    cboTargetBook.AddItem "This Book (" & WB.name & ")"
                Else
                    cboSourceBook.AddItem WB.name
                    cboTargetBook.AddItem WB.name
                End If
                Exit For 'once you find a visible window, then move on to next workbook
                'cboSourceBook.AddItem WB.Name
                'cboTargetBook.AddItem WB.Name
            End If
        Next idx
        Debug.Print WB.name
    Next

    Exit Sub
    
End Sub



Private Sub cboSourceBook_Change()
    
    If cboSourceBook.ListIndex = -1 Then Exit Sub
    
    If cboSourceBook.Value = "This Book (" & ActiveWorkbook.name & ")" Then
        Set objSourceBook = ActiveWorkbook
    Else
        Set objSourceBook = Workbooks(cboSourceBook.Value)
    End If
    
    LoadAllSheets "Source"       ' load the source sheets into the poulldown

    
End Sub

Private Sub cboTargetBook_Change()
    
    If cboTargetBook.ListIndex = -1 Then Exit Sub
    
    If cboTargetBook.Value = "This Book (" & ActiveWorkbook.name & ")" Then
        Set objTargetBook = ActiveWorkbook
    Else
        Set objTargetBook = Workbooks(cboTargetBook.Value)
    End If
    
    LoadAllSheets "Target"       ' load the source sheets into the poulldown

    
End Sub



Sub cpyRange() ' Copy one range for example---works like this

Dim SourceSheet As Excel.Worksheet
Dim SourceWorkbook As Excel.Workbook
Dim RangeName As String
Dim TargetWorkbook As Excel.Workbook
Dim TargetSheet As Excel.Worksheet

Application.ScreenUpdating = False

'Set SourceWorkbook = Workbooks.Open("C:\Users\~User\~Folder\~File.xlsm") ' For use as a macro with a known full path.
'SourceWorkbook.Sheets("~SheetName~").Range("Ride_Ht_LF").Copy 'Used with known path
'Set TargetWorkbook = Workbooks(cboTargetBook.Value) 'Would Rather be able to use the combo box here.
'Set TargetSheet = TargetWorkbook.Worksheets(cboTargetSheet.Value) 'Would Rather be able to use the combo box here.
Set SourceWorkbook = Workbooks(cboSourceBook.Value) 'For use with a combo box selection.
Set SourceSheet = SourceWorkbook.Worksheets(cboSourceSheet.Value) 'For use with a combo box selection.
SourceSheet.Range("Ride_Ht_LF").Copy
Set TargetWorkbook = ThisWorkbook 'Want to be able to select with cboTargetBook.Value ' Problem occurs with change of target in combo box.
Set TargetSheet = TargetWorkbook.Sheets("Interface") ' Problem occurs with change of target in combo box.
TargetSheet.Range("Ride_Ht_LF").PasteSpecial Paste:=xlPasteValues

End Sub

Thanks,
Bud

[Tinbendr]

Yes, the 'This Book' and 'This Sheet' are the culprits. You'll have to test for and strip the phrases before setting the object.

This makes this macro really complicated.

I went against my principles and recreated the workbook. See attached.