Issue with Static Combobox

[bcmk29]

I have the following code to create a combobox statically and it works fine. But when I try to fill the box with a range in another sheet it throws me an error. I tried 3 methods below in bold. Can someone help?

Private Sub CommandButton1_Click()
Dim Height As Long
Dim n As Integer

LR = Cells(Rows.Count, 16).End(xlUp).Row
Sheets("Sheet3").Range("S1").Value = Sheets("Sheet3").Range("S1").Value + 1
n = Sheets("Sheet3").Range("S1").Value
Height = 187
Set theComboBox = DataMap.Controls.Add("Forms.combobox.1", True)
With theComboBox
.Name = "Combobox" & n
.Left = 18
.Width = 80
.Top = Height + (25 * n)
.ListFillRange = Sheets("Sheet3").Range("P1:P" & LR).Value
ComboBox1.List = Sheets("Sheet3").Range("P1:P" & LR).Value
ComboBoxn.List = Sheets("Sheet3").Range("P1:P" & LR).Value
End With

 

[Anthony47]

For example

 .RowSource = "Sheet3!P1:P" & LR

 

[bcmk29]

.RowSource = "Sheet3!P1:P" & LR

Thanks a lot for your input that works as expected.

In the code below I'm trying to import the range mentioned in cell Q1
Set rng = Application.InputBox(Title:="Please select", Prompt:="First Name Column", Type:=8)
'Set rng = ThisWorkbook.Sheets("Sheet3").Range("Q1")
MsgBox rng
On Error GoTo 0
If rng Is Nothing Then Exit Sub
If rng.Rows.Count = 1 Then
MsgBox "Please select a entire Column to import.", vbOKOnly
GoTo ok1
Exit Sub
End If
ThisWorkbook.Worksheets(2).Activate
Importworkbook.Worksheets(1).Range(rng.Address).Copy ThisWorkbook.Worksheets(2).Range("A1")
Application.ScreenUpdating = True

 

[bcmk29]

In the code below I'm trying to import the range mentioned in cell Q1 but the importworksheet option doesn't allow me. The same works well if I get the range in the input box. Please help.

Set rng = Application.InputBox(Title:="Please select", Prompt:="First Name Column", Type:=8)
'Set rng = ThisWorkbook.Sheets("Sheet3").Range("Q1")
MsgBox rng
On Error GoTo 0
If rng Is Nothing Then Exit Sub
If rng.Rows.Count = 1 Then
MsgBox "Please select a entire Column to import.", vbOKOnly
GoTo ok1
Exit Sub
End If
ThisWorkbook.Worksheets(2).Activate
Importworkbook.Worksheets(1).Range(rng.Address).Copy ThisWorkbook.Worksheets(2).Range("A1")
Application.ScreenUpdating = True

 

[Anthony47]

What is Importworkbook?

 

[bcmk29]

What is Importworkbook?

Here's the complete code.

Dim LR As Long
Dim FileLocation As String
Dim rng As Range

LR = Cells(Rows.Count, 1).End(xlUp).Row
If LR = 1 Then Else GoTo ok
FileLocation = Application.GetOpenFilename("(*.xlsx),")
If FileLocation = "False" Then
MsgBox "No File/Data selected to import.", vbOKOnly
Exit Sub
End If
Set Importworkbook = Workbooks.Open(Filename:=FileLocation)
On Error Resume Next
ok1:
Set rng = Application.InputBox(Title:="Please select", Prompt:="First Name Column", Type:=8)
'Set rng = ThisWorkbook.Sheets("Sheet3").Range("Q1")
MsgBox rng
On Error GoTo 0
If rng Is Nothing Then Exit Sub
If rng.Rows.Count = 1 Then
MsgBox "Please select a entire Column to import.", vbOKOnly
GoTo ok1
Exit Sub
End If
ThisWorkbook.Worksheets(2).Activate
Importworkbook.Worksheets(1).Range(rng.Address).Copy ThisWorkbook.Worksheets(2).Range("A1")
Application.ScreenUpdating = True

 

[Anthony47]

What this macro should do, what happens when you execute it, what that was expected do not happen, is it related to the combobox and how?
Also, please use tags for readability of the code

 

[bcmk29]

What this macro should do, what happens when you execute it, what that was expected do not happen, is it related to the combobox and how?
Also, please use tags for readability of the code

I'm trying to import range (A:A) from Importworkbook to ThisWorkbook.Worksheets(2).Range("A1"). The code works fine when I select the range via Input Box.

Now I need to import the range mentioned in cell Q1 to ThisWorkbook.Worksheets(2).Range("A1") without the help of Inputbox.
I changed the code with InputBox to what's below (in bold) to make it work but no luck

Set rng = Application.InputBox(Title:="Please select", Prompt:="First Name Column", Type:=8)
Set abc= ThisWorkbook.Sheets("Sheet3").Range("Q1")
Importworkbook.Worksheets(1).Range(abc).Copy ThisWorkbook.Worksheets(2).Range("A1")

Sorry if I'm not being clear.

 

[Anthony47]

Now I need to import the range mentioned in cell Q1 to ThisWorkbook.Worksheets(2).Range("A1") without the help of Inputbox.

If Q1 is on the currently active sheet you might use

Importworkbook.Worksheets(1).Range(Range("Q1").Value).Copy ThisWorkbook.Worksheets(2).Range("A1")

(otherwise you need to fully qualify which Q1, ie specifying workbook and worksheet)

 

[bcmk29]

(otherwise you need to fully qualify which Q1, ie specifying workbook and worksheet)

I tried the code but it returns the following error.

Run-time error '424':
Object required