ComboBox set focus

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
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,376
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,376
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,101,904
Messages
5,483,646
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top