Could you check my userform code please

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
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: 10

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
ipbr21054 said:
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
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
ipbr21054 said:
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.
Click to expand...

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
 
Upvote 0
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: 2
  • 7236.jpg
    7236.jpg
    31.2 KB · Views: 3
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Value in userform isnt entered correctly when sent to worksheet
Replies
6
Views
401
ipbr21054
I
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
63
ipbr21054
I
I
  • Question
Unable to close form with existing code creating a no go loop
Replies
6
Views
435
Micron
M
I
  • Question
Userform fails to set focus on textbox
Replies
5
Views
502
breynolds0431
B
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
177
ipbr21054
I

Forum statistics

Threads
1,214,537
Messages
6,120,096
Members
448,944
Latest member
SarahSomethingExcel100

Share this page

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
Back
Top