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]

If you prefer stay with the sheet Index:

Dim IB As Long                'IB now should be a Number
'..
'..
If Sheets.Count > 1 Then
''    Importworkbook.Activate
reIB:
    IB = Application.InputBox("Enter Valid worksheet number", "Worksheet selection", , , , , , 1)
'    if ib=0 then ????     'What to do if Cancel il pressed???
    If IB > Sheets.Count Then
        GoTo reIB
    End If
    Sheets(IB).Select
End If
ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Copy
'other code

 

[bcmk29]

This code goes back to the same old way of entering the sheet number (1,2,3) rather than allowing the user to select the sheet itself.

 

[Anthony47]

So you would like to use an interactive interface...

Download the following file: ShSelect.zip

Expand the zip file and save the two files that it contains (ShSelect.frm and a ShSelect.frx)
Now go to the vba project of your file, and use Menu /File /Import file; select the file ShSelect.frm and complete the importing

Now you have a userform whose name is ShSelect
Go to the module that contains the macro
1) on top of the module, before any other instruction, insert

Public SheetSelected As Boolean

2) in the position where you need to ask for selecting the sheet, insert the following code:

Dim myTim As Single, tOut As Long

    tOut = 30       'Time out, in Seconds
    ShSelect.Show vbModeless
reASK:
    myTim = Timer
    Do
        If Timer > (myTim + tOut) Then Exit Do
        If SheetSelected Then
            Exit Do
        End If
        DoEvents
    Loop
'next code
'

When the macro execcutes, these instructions will set a timeout of 30 seconds (you may choose a different value), then the userform will show and ask for selecting a sheet; a button is available to confirm the selection.
If the button is pressed before the timeout expires, the block of code will be terminated with SheetSelected=True
If the timeout expires without the button pressed then the loop will terminate with SheetSelected=False
You can check tSheetSelected status and do whichever action is necessary in case a selection hasn't been made (a messagebox and then repeating the loop; use a default sheet; abort the process)

Try this penultimate method...

 

[bcmk29]

Thanks, Anthony47 that works as expected. I'll close this thread now.

I need your help with 2 more items I'll open a new thread please help if you can.

 

[Anthony47]

Thank you for the feedback
If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

 

[bcmk29]

Thank you for the feedback
If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

I've already marked this thread as Resolved. Let me know if it's not.

Here's the link to my next thread appreciate your help.

For Loop with multiple condition

Here's my code to copy the range available in SH and paste it into A1. This works fine for the first time, for the second time code pastes the copied value in A1 again I need that to be looped as B1, C1, D1..etc Please help. SH = ThisWorkbook.Worksheets(4).Cells(Rows.Count, 19).End(xlUp).Row -...

www.mrexcel.com

 

[bcmk29]

I've already marked this thread as Resolved. Let me know if it's not.

Here's the link to my next thread appreciate your help.

For Loop with multiple condition

Here's my code to copy the range available in SH and paste it into A1. This works fine for the first time, for the second time code pastes the copied value in A1 again I need that to be looped as B1, C1, D1..etc Please help. SH = ThisWorkbook.Worksheets(4).Cells(Rows.Count, 19).End(xlUp).Row -...

www.mrexcel.com

The previous For Loop question has been answered.
Can you help me with the below?

Check Combobox is empty

With the below code, I need 2 help. 1. I would like to check if all the Combobox has value or not. In the screenshot, you can see I've created 2 new Combobox statically. When I execute this code the MsgBox throws the value Label and the code exits. I'm not sure where it's fetched from and the...

www.mrexcel.com