ComboBox set focus

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Hi,
Can you give some pointers please.
I have a userform on a worksheet then opens automatically when you visit the page.

Ive looked for this set focus code but dont find it anywhere.
Can this be written another way as opposed to set focus.

I just cant seem to find where its being controlled.

Thanks
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
When i right click and view code on userform i see the following,

Code:
Private Sub CommandButton1_Click()    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "CLONING TRANSFER SHEET"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
    
    With ThisWorkbook.Worksheets("CLONING")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False
    With Sheets("CLONING")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:F" & x).Sort key1:=Range("A4"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("CLONING").Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Unload CloningForm
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Hi,
I am still looking for this error but so far no joy.
Below is the two sets of information to assist you if i may.

When i come to worksheet CLONING a userform opens up.
The Focus is set on ComboBox1

Here is the code

Code:
Private Sub CommandButton1_Click()    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "CLONING TRANSFER SHEET"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
    
    With ThisWorkbook.Worksheets("CLONING")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False
    With Sheets("CLONING")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:F" & x).Sort key1:=Range("A4"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("CLONING").Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Unload CloningForm
End Sub
When i come to the worksheet called SKPLIST my userform opens up.
Nothing has the set focus & i would like it also to be set on ComboBox1

Here is the code.

Code:
Private Sub CommandButton1_Click()    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "SKP IMMO LIST TRANSFER"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
    
    With ThisWorkbook.Worksheets("SKPLIST")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
    
    Application.ScreenUpdating = False
    With Sheets("SKPLIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:F" & x).Sort key1:=Range("A4"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("SKPLIST").Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Unload SkpImmoListForm
End Sub
Thanks for your time & can you advise why or how do i set focus on ComboBox1 on the SKPLIST
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
When your userform first opens up, unless specified in the initialization macro, the focus will be set according to the first item in the forms tab order.

In design mode, right click on the userform and select tab order.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Thanks it worked as you said.

I didnt know that but will now remember it

Cheers again,took me hours to not find anything
 

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,469
Members
407,602
Latest member
clang663

This Week's Hot Topics

Top