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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Does this work for you?

Code: 
If ComboBox3.ListIndex >= 0 Then
    iRow = ComboBox3.ListIndex + 1
Else
    iRow = ws.Cells(Rows.Count, 2) _</SPAN>
            .End(xlUp).Offset(1, 0).Row</SPAN>
End If
 
Upvote 0
Thank you for the reply I appreciate it...Does this code put the data on a new line if combobox3 does not match? I only want to put it on the line that combox3 matches column 6 if no match then error/text box.
 
Upvote 0
If nothing is selected in ComboBox3 it uses the next blank row. Otherwise it uses the row of the item selected in the ComboBox. You may need to adjust iRow to take account of headers etc if the entries in ComboBox3 don't start at row 1.
 
Upvote 0
Thank you... looks like we almost got it.. Its entering the data one row up from the matching cell. I need it on the same row as the match.
 
Upvote 0
Thank you!!! for the help I appreciated it..... You were 100% correct it was the a few adjustments to the irow... below is the code it's working perfectly.

Code: 
Private Sub CommandButton13_Click()
Dim rNextCl As Range
 
Set rNextCl = Worksheets("Main").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Worksheets("Main").Activate
rNextCl.Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Main")
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.Unprotect
  
 [COLOR=#00ff00]If ComboBox12.ListIndex >= 0 Then
    iRow = ComboBox12.ListIndex +[/COLOR][COLOR=#ff0000] 2
[/COLOR][COLOR=#00ff00]Else
    iRow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row
[/COLOR]End If
  
If Me.ComboBox12 = "" Then
MsgBox ("All Fields Must be Completed")
  
        Exit Sub
    End If
 
 
'copy the data to the database
With ws
   
    .Cells(iRow, 7).Value = Me.TextBox32.Value
    .Cells(iRow, 8).Value = Me.TextBox33.Value
    .Cells(iRow, 9).Value = Me.ComboBox10.Value
    .Cells(iRow, 10).Value = Me.TextBox35.Value
    .Cells(iRow, 11).Value = Me.TextBox36.Value
    .Cells(iRow, 12).Value = Me.TextBox37.Value
    .Cells(iRow, 13).Value = Me.TextBox30.Value
    .Cells(iRow, 14).Value = Me.TextBox31.Value
    .Cells(iRow, 15).Value = Me.TextBox38.Value
   .Cells(iRow, 16).Value = Me.TextBox39.Value
   .Cells(iRow, 17).Value = Me.ComboBox11.Value
   
    With .Cells(iRow, 1).Resize(1, 18).Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

'clear the data
   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.ComboBox9.Value = ""
      Me.ComboBox10.Value = ""
       Me.ComboBox11.Value = ""
        Me.ComboBox12.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 = ""
    Me.TextBox28.Value = ""
    Me.TextBox29.Value = ""
    Me.TextBox30.Value = ""
    Me.TextBox31.Value = ""
    Me.TextBox32.Value = ""
    Me.TextBox33.Value = ""
    Me.TextBox34.Value = ""
    Me.TextBox35.Value = ""
    Me.TextBox36.Value = ""
    Me.TextBox37.Value = ""
    Me.TextBox38.Value = ""
    Me.TextBox39.Value = ""
    Me.TextBox40.Value = ""
    Me.TextBox41.Value = ""
    Me.TextBox42.Value = ""
    Me.TextBox43.Value = ""
     Me.TextBox44.Value = ""
    
       
   edate1.Value = Now
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    'ActiveSheet.Unprotect
End With
OptionButton2.Value = True
'Set no car as OOB failure
OptionButton4.Value = True
OptionButton6.Value = True
'Set no car as OOB failure
OptionButton8.Value = True
End Sub
 
Upvote 0
This is the same exact scenario I'm going through except I am using a text box. I'm a newbie, but I'm guessing I won't be using "ComboBox12.ListIndex". my text box is named txtID. The user will input a unique id in that text box that will always be a numeric number. I want the form to look for that value in column J and then place a value the user inputed in txtMin into column H in the same row.

Any help would be greatly appreciated.
 
Upvote 0
Why don't you use a ComboBox filled with the items in column J? That would be much easier for the user and you.
 
Upvote 0
I didn't know that was possible. It's amazing what can be done with Excel/VBA. Would you be able to guide me on how I would do that?

edit: To clarify, Column J will have new rows added to it daily. Are you saying I should manually add these to the combo box or is there a way to automatically add the last "x" rows from this column into a combo box?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
User Form Textbox looping
2
Replies
12
Views
599
sjtjr73
S
S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
144
Domenic
D
J
  • Question
Listbox not showing in sent email
Replies
1
Views
133
Micron
M
I
  • Question
Move existing row of value to another selected row
Replies
1
Views
150
ipbr21054
I
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
229
amoverton2
A

Forum statistics

Threads
1,215,310
Messages
6,124,188
Members
449,147
Latest member
sweetkt327

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