User Form Data insert into sheet ??? "specific line based on text box"

J

Javi

Active Member
Joined
May 26, 2011
Messages
440
Hi All, </SPAN>
The below code “CMD_Add_Click” allows me to fill in a user form and have the data enter into Sheet “Main” on the first empty row based on column 2 in total the code works great.
</SPAN>
I need to add a new user form that will allow me to do the following but I am totally stuck.
</SPAN>
What I need to do now is to input additional items on an existing row (not the next empty one) based on the matching value of Combox3. to column 6 on sheets "Main". There should never be any duplicates or repeating values in column 6. I was hoping it would be a simple edit to the below code that I have identified in red.
</SPAN>
Thank you in advance any suggestion or help would be greatly appreciated.


I apologize in advance for not using specific identifiers for text boxes I got carried away and was too far along to correct the generic names so go ahead and laugh. </SPAN>

Code: 
‘Finds Next empty line in row 2 works great </SPAN>
[COLOR=#ff0000]iRow = ws.Cells(Rows.Count, 2) _[/COLOR]</SPAN>[COLOR=#ff0000]
            .End(xlUp).Offset(1, 0).Row[/COLOR]</SPAN>[COLOR=#ff0000]
[/COLOR]

Code: 
‘This was one of my attempts trying to fine a specific row Based on the value of textbox3 no this did not work I tried 100 versions of it NO GO!!!!!</SPAN>
iRow = ws.Cells(combobox3.value, 2) _</SPAN>
            .End(xlUp).Offset(0, 0).Row</SPAN>

Below is my full code for entering data to the data to the next empty line works well is set in a command button on the use of form.

Code: 
Private Sub CMD_Add_Click()
            Dim rNextCl As Range
 
            Set rNextCl = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
            Worksheets("Main").Activate
            rNextCl.Select
            Dim iRow As Long
            Dim ws As Worksheet
            Set ws = Worksheets("Main")
    
            ActiveSheet.Unprotect
'find  first empty row in database
           [COLOR=#ff0000]iRow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row[/COLOR]
'check for a needed textbox data
            If Me.edate1 = "" Or Me.ComboBox1 = "" Or Me.TextBox1 = "" Or Me.TextBox2 = "" _
            Or Me.TextBox3 = "" Or Me.TextBox4 = "" Or Me.TextBox5 = "" Or Me.ComboBox2 = "" _
            Or Me.TextBox6 = "" Or Me.TextBox7 = "" Or Me.TextBox8 = "" Then
            MsgBox ("All Fields Must be Completed")
  
        Exit Sub
    End If
'copy the data to the database Sheets "Main"
        
        With ws
            .Cells(iRow, 2).Value = Me.edate1.Value
            .Cells(iRow, 3).Value = Me.ComboBox1.Value
            .Cells(iRow, 4).Value = Me.TextBox1.Value
            .Cells(iRow, 5).Value = Me.TextBox2.Value
            .Cells(iRow, 6).Value = Me.TextBox3.Value
            .Cells(iRow, 7).Value = Me.TextBox4.Value
            .Cells(iRow, 8).Value = Me.TextBox5.Value
            .Cells(iRow, 9).Value = Me.ComboBox2.Value
            .Cells(iRow, 10).Value = Me.TextBox6.Value
            .Cells(iRow, 11).Value = Me.TextBox7.Value
            .Cells(iRow, 12).Value = Me.TextBox8.Value
            .Cells(iRow, 13).Value = Me.TextBox10.Value
            .Cells(iRow, 14).Value = Me.TextBox11.Value
            .Cells(iRow, 15).Value = Me.TextBox9.Value
   
 'Add Border
 
            With .Cells(iRow, 1).Resize(1, 15).Borders
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
'Add one to column "A" for the line item number
            ws.Cells(iRow, 1).Value = ws.Cells(iRow - 1, 1).Value + 1
'clear the data in text and combo boxes after entry
            Me.edate1.Value = ""
            Me.ComboBox1.Value = ""
            Me.TextBox1.Value = ""
            Me.TextBox2.Value = ""
            Me.TextBox3.Value = ""
            Me.TextBox4.Value = ""
            Me.TextBox5.Value = ""
            Me.ComboBox2.Value = ""
            Me.TextBox6.Value = ""
            Me.TextBox7.Value = ""
            Me.TextBox8.Value = ""
   
      
            Me.ComboBox3.Value = ""
            Me.ComboBox4.Value = ""
            Me.ComboBox5.Value = ""
            Me.ComboBox6.Value = ""
            Me.ComboBox7.Value = ""
            Me.ComboBox8.Value = ""
    
    
            Me.TextBox9.Value = ""
            Me.TextBox12.Value = ""
            Me.TextBox13.Value = ""
            Me.TextBox16.Value = ""
            Me.TextBox19.Value = ""
            Me.TextBox20.Value = ""
            Me.TextBox21.Value = ""
            Me.TextBox22.Value = ""
            Me.TextBox23.Value = ""
            Me.TextBox24.Value = ""
            Me.TextBox25.Value = ""
            Me.TextBox26.Value = ""
            Me.TextBox27.Value = ""
        
'Adds date in userform ready for next entry
   
            edate1.Value = Now
                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    
        End With
'Set Option button 2 to no
        
        OptionButton2.Value = True
 
'Set Option button to no
        OptionButton4.Value = True

End Sub
 
Last edited:
Sort by date Sort by votes
That worked wonderfully. Thank you!!

The last bit of this userform I need to work is changing a label to display the contents of a cell on the same row as the unique id chosen in the combo box. Is that possible? Below is the code as I have it now. The label is lblUser.

Code: 
Private Sub UserForm_Initialize()
'Populate Color combo box.
Dim rngID As Range

Dim ws As Worksheet
Set ws = Worksheets("Raw Data")
For Each rngID In ws.Range("UID")
Me.cboID.AddItem rngID.Value
Next rngID
End Sub



Private Sub cmdSave_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Raw Data")
    
    'vlookup UNIQUE ID and add minutes to that row.
    If cboID.ListIndex >= 0 Then
        iRow = cboID.ListIndex + 3
    Else
        iRow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row
    End If
    
    'Throw error message if a field is not filled out.

    If Me.cboID = "" Then
        MsgBox ("Missing UNIQUE ID Field. Please fill this out and try again.")
          
        Exit Sub
    End If
    If Me.txtMin = "" Then
        MsgBox ("Missing Minutes Field. Please fill this out and try again.")
          
        Exit Sub
    End If
    
    
    With ws
        .Cells(iRow, 9).Value = Me.txtMin.Value

    
    End With
   
    
    'Clear input controls.
    Me.cboID.Value = ""
    Me.txtMin.Value = ""
    
        
End Sub

Private Sub cmdExit_Click()
    'Close UserForm.
    Unload Me
    
End Sub
 
Last edited:
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Code: 
Private Sub cboID_Change()
    Dim i As Long
    i = Me.cboID.ListIndex
    If i = -1 Then
        Me.lblAnalystC.Caption = "Please Select a UNIQUE ID"
    Else
        'row numbers are stored in the combobox's column 2 (hidden)
        lblAnalystC.Caption = Sheets("Raw Data").Range("G" & i+3).Value
    End If
End Sub

The above is actually working for those who are interested. In my case I had to have the "i+3" because i have headers for my data.

Thanks for all of your help!
 
Last edited:
Upvote 0
Just one more thing I'd like with regard to this combobox. Right now the newest added cells will appear at the bottom. Can I reverse that have the newest above?
 
Upvote 0
You can loop backwards through the Range and use AddItem to populate the ComboBox. You will need to amend your update code to take into account that ListIndex will be in reverse order.
 
Upvote 0
Code: 
Private Sub UserForm_Initialize()
'Populate Unique ID combo box.
Dim rngID As Range

Dim WS As Worksheet
Set WS = Worksheets("Raw Data")
For Each rngID In WS.Range("UID")
Me.cboID.AddItem rngID.Value
Next rngID
End Sub

I'm not understanding. This is the code I have now. Are you saying I have to make changes here or on the defined names group that I created based off of the link you provided earlier?
 
Upvote 0
Like this?

Code: 
Private Sub UserForm_Initialize()
'   Populate Unique ID combo box.
    Dim i As Long
    Dim WS As Worksheet
    Set WS = Worksheets("Raw Data")
    With WS.Range("UID")
        For i = .Rows.Count To 1 Step -1
            Me.cboID.AddItem .Cells(i, 1).Value
        Next i
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help Needed
2
Replies
13
Views
289
kwavic
K
S
  • Solved
User Form Textbox looping
2
Replies
12
Views
928
sjtjr73
S
S
  • Question Question
User Form not Loading
Replies
5
Views
147
StevieMP
S
I
  • Solved
Last row in table resizes after new row inserted at top
Replies
3
Views
200
ipbr21054
I
I
  • Solved
Correct sytax for yes / no Msgbox
2
Replies
10
Views
391
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,223,099
Messages
6,170,114
Members
452,302
Latest member
TaMere

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