Could you check my userform code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,931
Office Version
  1. 2007
Platform
  1. Windows
Hi,

I have a userform which when i use the command button it should transfer the data to the worksheet.

The code is shown below
I complete the userform & press the command button.

I then see the success message BUT there is nothing on the worksheet.
row 2 is hidden.
What did i miss.
Thanks


Worksheet can be downloaded here.
MOTORCYCLE CLONING TEST.xlsm

VBA Code:
Private Sub CommandButton1_Click()
      
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Array(TextBox1, Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6, Me.ComboBox7)
    Next i
    
    With ThisWorkbook.Worksheets("DETAILS")
      .Range("B3").EntireRow.Insert Shift:=xlDown
      .Range("B3:I3").Borders.Weight = xlThin
      .Cells(3, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr


      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess
      
    End With
    ActiveWorkbook.Save
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload MotorcycleCloningForm
    
End Sub
 

Attachments

  • 7224.jpg
    7224.jpg
    203.9 KB · Views: 8

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,931
Office Version
  1. 2007
Platform
  1. Windows
Ive now got here but stuck.
I will await a reply now thank

VBA Code:
Private Sub CommandButton1_Click()
      
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
    ControlsArr(i) = Controls("ComboBox", "TextBox") & i).Value
    
    Next i
      ControlsArr(i) = Array(TextBox1, Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6, Me.ComboBox7)

    With ThisWorkbook.Worksheets("DETAILS")
      .Range("B3").EntireRow.Insert Shift:=xlDown
      .Range("B3:I3").Borders.Weight = xlThin
      .Cells(3, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr


      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess
      
    End With
    ActiveWorkbook.Save
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload MotorcycleCloningForm
    
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,509
Office Version
  1. 2019
Platform
  1. Windows
untested but try this update to your code & see if does what you want

VBA Code:
Private Sub CommandButton1_Click()
    
    Dim i As Long, x As Long
    Dim ControlsArr(0 To 7) As Variant
    
    
    ControlsArr(0) = Me.TextBox1.Value
    For i = 1 To 7
        ControlsArr(i) = Me.Controls("ComboBox" & i).Value
    Next i
        
        
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
        With ThisWorkbook.Worksheets("DETAILS")
            .Range("B3").EntireRow.Insert Shift:=xlDown
            .Range("B3:I3").Borders.Weight = xlThin
            
            .Cells(3, 2).Resize(, UBound(ControlsArr) + 1).Value = ControlsArr
            
            
            
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess
            
        End With
        
        ActiveWorkbook.Save
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
        
    Unload Me
        
End Sub

Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,931
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Yes it transfers now to the worksheet thanks.
Can i ask,
What in the code is incorrect as it should transfer to the worksheet then sort A-Z column B

Thanks
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,509
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,
Yes it transfers now to the worksheet thanks.
Can i ask,
What in the code is incorrect as it should transfer to the worksheet then sort A-Z column B

Thanks

try replacing this bit of code

VBA Code:
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess

with this

VBA Code:
            x = .Cells(.Rows.Count, 2).End(xlUp).Row
            .Range("B2:I" & x).Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlGuess

Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,931
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Thanks for your time.

This seems to have done the trick.

Rich (BB code):
Private Sub CommandButton1_Click()
    
    Dim i As Long, x As Long
    Dim ControlsArr(0 To 7) As Variant
    
    
    ControlsArr(0) = Me.TextBox1.Value
    For i = 1 To 7
        ControlsArr(i) = Me.Controls("ComboBox" & i).Value
    Next i
        
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
        With ThisWorkbook.Worksheets("DETAILS")
            .Range("A3").EntireRow.Insert Shift:=xlDown
            .Range("A3:H3").Borders.Weight = xlThin
            
            .Cells(3, 1).Resize(, UBound(ControlsArr) + 1).Value = ControlsArr
            

        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
       .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess
       .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
       Range("A3").Select
       End With
        
        ActiveWorkbook.Save
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
        
    Unload Me
        
End Sub

Need to pop out so when im back i need to add code so all fields are complete before transfer,if your about later to advise on my attempt.

Thanks again
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,509
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,
Thanks for your time.

This seems to have done the trick.
Need to pop out so when im back i need to add code so all fields are complete before transfer,if your about later to advise on my attempt.

Just post your questions to forum - plenty here to assist - just be mindful this is a free help site & may have to wait a little while for response(s)

Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,931
Office Version
  1. 2007
Platform
  1. Windows
Afternoon all,

Below is the working code with nothing to check all fields are complete.

Rich (BB code):
Private Sub CommandButton1_Click()
    
    Dim i As Long, x As Long
    Dim ControlsArr(0 To 7) As Variant
    
    
    ControlsArr(0) = Me.TextBox1.Value
    For i = 1 To 7
        ControlsArr(i) = Me.Controls("ComboBox" & i).Value
    Next i
        
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
        With ThisWorkbook.Worksheets("DETAILS")
            .Range("A3").EntireRow.Insert Shift:=xlDown
            .Range("A3:H3").Borders.Weight = xlThin
            
            .Cells(3, 1).Resize(, UBound(ControlsArr) + 1).Value = ControlsArr
            

        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
       .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess
       .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
       Range("A3").Select
       End With
        
        ActiveWorkbook.Save
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
        
    Unload Me
        
End Sub


Then this morning i added some code to make sure all fields are completed before transfer from userfom to worksheet.
But i get a RTE which appears when i try to send to worksheet.
Please can you advise what ive missed out.

Many Thanks.


Below is the code.
Red section is this mornings added code.

Rich (BB code):
Private Sub CommandButton1_Click()
    
    Dim i As Long, x As Long
    Dim ControlsArr(0 To 7) As Variant
    
    
    ControlsArr(0) = Me.TextBox1.Value
    For i = 1 To 7
           With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "   ALL FIELDS MUST BE COMPLETED" & vbNewLine & "BEFORE TRANSFERING TO WORKSHEET", vbCritical, "MOTORCYCLE CLONING LIST TRANSFER"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i

        ControlsArr(i) = Me.Controls("ComboBox" & "TextBox1" & i).Value
    
        
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
        With ThisWorkbook.Worksheets("DETAILS")
            .Range("A3").EntireRow.Insert Shift:=xlDown
            .Range("A3:H3").Borders.Weight = xlThin
            
            .Cells(3, 1).Resize(, UBound(ControlsArr) + 1).Value = ControlsArr
            

        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
       .Range("A2:I" & x).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlGuess
       .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
       Range("A3").Select
       End With
        
        ActiveWorkbook.Save
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox "DATABASE HAS BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
        
    Unload Me
        
End Sub
 

Attachments

  • 7235.jpg
    7235.jpg
    31.3 KB · Views: 1
  • 7236.jpg
    7236.jpg
    31.2 KB · Views: 1

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,509
Office Version
  1. 2019
Platform
  1. Windows
Replace this section of code

VBA Code:
Dim i As Long, x As Long
    Dim ControlsArr(0 To 7) As Variant
   
   
    ControlsArr(0) = Me.TextBox1.Value
    For i = 1 To 7
           With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
MsgBox " ALL FIELDS MUST BE COMPLETED" & vbNewLine & "BEFORE TRANSFERING TO WORKSHEET", vbCritical, "MOTORCYCLE CLONING LIST TRANSFER"
.SetFocus
Exit Sub
End If
End With
    Next i

        ControlsArr(i) = Me.Controls("ComboBox" & "TextBox1" & i).Value


with this


VBA Code:
   Dim i As Long, x As Long
    Dim m As Variant
    Dim ControlsArr(0 To 7) As Variant
    
    
    ControlsArr(0) = Me.TextBox1.Value
    For i = 1 To 7
        ControlsArr(i) = Me.Controls("ComboBox" & i).Value
    Next i
    
    m = Application.Match("", ControlsArr, 0)
    If Not IsError(m) Then
        MsgBox "ALL FIELDS MUST BE COMPLETED" & vbNewLine & _
            "BEFORE TRANSFERING TO WORKSHEET", vbCritical, "MOTORCYCLE CLONING LIST TRANSFER"
               Me.Controls(IIf(m = 1, "TextBox", "ComboBox") & IIf(m > 1, m - 1, m)).SetFocus
        Exit Sub
    End If

and see if it does what you want

Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,931
Office Version
  1. 2007
Platform
  1. Windows
Yes it does many thanks.

Got a bit lost with my attempt
 

Watch MrExcel Video

Forum statistics

Threads
1,129,493
Messages
5,636,639
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top