Results 1 to 8 of 8

Thread: Addition to existing working code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,060
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Addition to existing working code

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

    Code:
    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.


    Code:
    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.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,894
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Addition to existing working code

    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.

    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("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
    Last edited by JLGWhiz; May 18th, 2019 at 05:11 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,060
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Addition to existing working code

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,894
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Addition to existing working code

    Quote Originally Posted by ipbr21054 View Post
    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
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,060
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Addition to existing working code

    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 ?
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,060
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Addition to existing working code

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,060
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Addition to existing working code

    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 ?
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  8. #8
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,060
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Addition to existing working code

    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 ?
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •