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

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

TIA
Bud

You're not assigning any objects, just values.

Please Login or Register  to view this content.

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.

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.

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.