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]

Whic is the full code?

 

[bcmk29]

Whic is the full code?

Here's the complete code that works fine via input box.

Sub DataImport()
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
End sub

Here are the code lines I modified to import the range mentioned in Q2

Importworkbook = ActiveWorkbook.Name
Importworkbook.Worksheets(2).Range(Range("Q2").Value).Copy ThisWorkbook.Worksheets(2).Range("A1")

If still unclear can you help me to modify the above working code to import from Imporworkbook to ThisWorkbook based on the range mentioned in Q2 in ThisWorkbook

 

[Anthony47]

If still unclear can you help me to modify the above working code to import from Imporworkbook to ThisWorkbook based on the range mentioned in Q2 in ThisWorkbook

So in Q2 (somewhere in ThisWorkbook) it's writte which range to import (eg: A5:C11)

Try using

Sub DataImport()
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)
ThisWorkbook.Worksheets(2).Activate
Importworkbook.Worksheets(1).Range(Sheets("????").Range("Q2").Value).Copy ThisWorkbook.Worksheets(2).Range("A1")       '<<< SEE MESSAGE
Application.ScreenUpdating = True
End Sub

Note: If you didn't specify which is the sheet that contains Q2; replace that "????" with the real name of the sheet, eg "Sheet2"

 

[bcmk29]

Thanks, the code works as expected your help really made my day.

Can you help me with the below code to modify the bold lines to select a Sheet via inputbox if there is more than 1 sheet present. Right now I'm collecting the sheet number and there's a chance to type the incorrect number.

Private Sub UserForm_Initialize()
Sheets("Sheet3").Range("S1").Value = 0
file = ActiveWorkbook.Name
Dim LR As Long
Dim FileLocation As String
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 selected to import.", vbOKOnly
Exit Sub
End If
End If
Set Importworkbook = Workbooks.Open(Filename:=FileLocation)
If Importworkbook.Sheets.Count > 1 Then
Importworkbook.Activate
IB = Application.InputBox("Enter worksheet number", "Worksheet selection", , , , , , 1)
MsgBox "Sheet" & IB & " has been selected"
Sheets("Sheet" & IB).Select
GoTo hi
Else
hi:
ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks(file).Activate
Sheets("Sheet3").Select
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
End If
End Sub

 

[Anthony47]

1) PLEASE USE TAGS to make your code readable
2) We should not add questions to the same description, admins will not be happy for this, so if you have a new problem then open a new thread.

Going to your question, replace the code from If Sheets.Count > 1 Then to the end with the folllowing:

Dim IB As Range         'Check if it has been declared differently
If Sheets.Count > 1 Then
''    Importworkbook.Activate
'    IB = Application.InputBox("Enter worksheet number", "Worksheet selection", , , , , , 1)
    Set IB = Application.InputBox("Select Range to copy (top cell)", "Select Range", , , , , , 8)
    If Not IB Is Nothing Then
        Application.Goto IB.Cells(1, 1)
    End If
End If
ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Copy
'Selection.Copy
Workbooks(file).Activate
Sheets("Sheet3").Select
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

End Sub

This does not ask for the Index of the worksheet, but for the selection of the worksheet + a range; this should be less error prone (in my opinion)

HOWEVER this is within a Userform handling: beware that Microsoft declares unreliable to modify the environment when a "modal" userform is open; and changing Workbook and worksheet is surely a change to the environment...
So my suggestion is that you open the Userform in "Modeless mode" using

myUserForm.Show vbModeless

 

[bcmk29]

1) PLEASE USE TAGS to make your code readable
2) We should not add questions to the same description, admins will not be happy for this, so if you have a new problem then open a new thread.

Going to your question, replace the code from If Sheets.Count > 1 Then to the end with the folllowing:

Dim IB As Range         'Check if it has been declared differently
If Sheets.Count > 1 Then
''    Importworkbook.Activate
'    IB = Application.InputBox("Enter worksheet number", "Worksheet selection", , , , , , 1)
    Set IB = Application.InputBox("Select Range to copy (top cell)", "Select Range", , , , , , 8)
    If Not IB Is Nothing Then
        Application.Goto IB.Cells(1, 1)
    End If
End If
ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Copy
'Selection.Copy
Workbooks(file).Activate
Sheets("Sheet3").Select
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

End Sub

This does not ask for the Index of the worksheet, but for the selection of the worksheet + a range; this should be less error prone (in my opinion)

HOWEVER this is within a Userform handling: beware that Microsoft declares unreliable to modify the environment when a "modal" userform is open; and changing Workbook and worksheet is surely a change to the environment...
So my suggestion is that you open the Userform in "Modeless mode" using

myUserForm.Show vbModeless

I'm not sure how to use TAGS and I'm new to this forum. I agree, I'll use a new thread next time.

The code allows me to select only a range, but not the sheet from where the data needs to be imported.

 

[Anthony47]

The code allows me to select only a range, but not the sheet from where the data needs to be imported

How did you test it?

For the tags:
-select the code, press the icon VBA; the tags will be added at the selected code

 

[bcmk29]

How did you test it?

For the tags:
-select the code, press the icon VBA; the tags will be added at the selected code

I did run the Macro in the spreadsheet and the input box came up I tried selecting sheet2 and sheet2 was displayed in the input box. But when i clicked ok it throws me an error.

I tried selecting range “A:A” in current sheet and it went through without any issue.

I observed one thing when i selected sheet2 i could also see sheet3 as selected meaning like selecting multiple sheets together. But sheet3 name didn’t show up in input box. Not sure this is causing the issue.

Am I doing something wrong here?

 

[Anthony47]

You should select the sheet and a range; the header should give some guidance.

I guess that Sheet3 is the starting sheet; the inputbox will select what you click on

 

[bcmk29]

You should select the sheet and a range; the header should give some guidance.

I guess that Sheet3 is the starting sheet; the inputbox will select what you click on

Got you, but I don’t want to select a range. I just need to pick a sheet if there’s more than 1 present. This is because I’m picking the range mentioned in the cell Q2 of different sheet. You remember the code you helped me earlier.