Addition to existing working code

[ipbr21054]

Hi,
The code supplied below transfers the input data from my userform to my worksheet.

Private Sub CommandButton1_Click()    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    
    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("D4").EntireRow.Insert Shift:=xlDown
        .Range("D4:I4").Borders.Weight = xlThin
        .Range("D4:I4").Value = ControlsArr
    End With
    
    For Each ctrl In ControlsArr
        ctrl.Text = ""
    Next




    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    
    Me.ComboBox1.SetFocus




End Sub

My worksheet is currently A4:F64

I have this code below on a button that sorts the worksheet in column A from A-Z
I would like but not sure how to include the sort function into the code supplied above.
So after the data is transfered to my worksheet it will then sort A-Z for me.

Private Sub ImmoCarButton_Click()    
    Dim x As Long
    
    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
    
End Sub

Many Thanks if you could advise please.

 

[JLGWhiz]

You are aware that the the two macros are for different worksheets, are you not? One is for Sheets("CLONING') and the other is for Sheets("SKPLIST").

If that makes no difference then here is the merged macros.

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("D4").EntireRow.Insert Shift:=xlDown
        .Range("D4:I4").Borders.Weight = xlThin
        .Range("D4:I4").Value = ControlsArr
    End With    
    For Each ctrl In ControlsArr
        ctrl.Text = ""
    Next    
    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"    
    Me.ComboBox1.SetFocus
End Sub

 

[ipbr21054]

Hi,
Thanks but i did not notice that,i see that i selected the incorrect items to supply here.

Using the above i have now made the code work.

Thanks

 

[JLGWhiz]

Hi,
Thanks but i did not notice that,i see that i selected the incorrect items to supply here.

Using the above i have now made the code work.

Thanks

You're welcome,
regards, JLG

 

[ipbr21054]

Hi,
I made another mistake when i said it sorts column A from A-Z,it actualy sorts column A numerically from small - large,small number being at top then larger numbers going down the page.

I have noticed that when i transfer to my worksheet it doesnt sort correct.

In this example the number transfered was 13 but it was sorted between 46 and 4c
So in column A i have the following as an example to show you
13
13
46
46
46
13 this is the incorrectly sorted number
4C
4C
4D
4D 60
8C
8C

Do you see an issue with the sort code ?

 

[ipbr21054]

I suppose the best thing is just to advise what i require to happen.

Once i complete the userform and send to my worksheet i need column A sorted.
Column A range is A3 and onwards.
Column A is numers & some numbers with letters.
Lowest number in cell A4 the as you go down the page the numbers will become higher.

Many thanks

 

[ipbr21054]

I have found the problem but not sure what i need to edit to fix it.

The cell in question once sent from user form to worksheet has a diagonal line in the top left corner.
If i type the same value manually in the cell problem solved.
As a test i submitted the form again,and the line appears,typing over it manually no line is shown & sort works perfect.

What is this line ?

 

[ipbr21054]

Morning,

Does anything spring to mind why when i transfer userform value from the text box to my worksheet the sort function is incorrect.
This is related to a date like so 1995
The data is sent to worksheet & the code is then sorted say 1990 down the page to say 2013

Problem being the value that was just sent to worksheet is always last,so like this 2007, 2011, 2012, 2013, 1995

Why ?