Data entry based on a button

F

figuare9

Board Regular
Joined
Oct 23, 2017
Messages
118
I have a workbook with 4 sheets.


  • First sheet is called "Main"

  • Second Sheet Is called "Heavy Used Belting"
  • Third Sheet Is called "Medium Used Belting"
  • Fourth Sheet Is called "Light Used Belting"

On "Main" I have 3 categories and 6 buttons. The categories correspond with the heavy, medium, and light used belting sheets. The 6 buttons are simply Add & Remove belts. (2 for each category)
Mostly Like this...


  • Heavy Used Belting
    • Add Belt
    • Remove Belt
  • Medium Used Belting
    • Add Belt
    • Remove Belt
  • Light Used Belting
    • Add Belt
    • Remove Belt

What I'm looking for, is to make data entry extremely useful for the end user. Essentially clicking a button to add or remove a row from a data table. So they can click on "Add belt" and have input boxes pop up sequentially for them to enter data in.

This is what the data table looks like for the "Heavy Used Belting" sheet. https://i.imgur.com/y5xcakZ.png

y5xcakZ.png




The Data table range is C4:O1000



Here's the harder part for me though...

I would like "Add Belt" button on the "Main" sheet to find the next roll number, and automatically create it. So, all of the Heavy Used Belts have a roll prefix of "HU", Medium as "MU", etc... So when you click "Add Belt" under the heavy used category, it will find the next "HU" Roll #, and add 1 to it when it creates the row.



Here's the workflow I'm trying to achieve based off the data table as shown above.



  1. User clicks "Add Belt" button on "Main" sheet for his desired category.
  2. Input Box = "Origin Location"
  3. Excel will automatically find the next available roll number and set it automatically
  4. Input Box = "Description"
  5. Input Box = "Length"
  6. Input Box = "Width"
  7. Input Box = "Total Weight"
  8. Input Box = "Price/lb"
  9. Input Box = "Price Paid For Whole"
  10. Input Box = "Comments"
  11. Input Box = "Date Received"
  12. Input Box = "SAP #"
  13. Input Box = "Purchase Order #"
  14. Input Box = "Location"
  15. A Message Box Pops Up and says "Your roll number is HU43" (<- example)


Mostly, the point of this is to keep the end user out of the data table. The end users for this barely know how to use a flip phone, and I'd like to make this as easy as possible for them.. However, I'm not really sure the best way to go about this. Perhaps there's an even easier way that you guys may know of?

I would be so grateful for any help on this! I'm kinda stumped on it myself.

Thank you,
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Re: Need help with data entry based on a button

Doing something to make it easy for users is not going to do an easy task for you.


One option is to put inputbox, another option, if you do not want to touch the sheet, is to capture with a userform, like this:

62479a7c889c24e5af558a72079ab968.jpg


Let me know if you need the userform and I help you create it.
 
Upvote 0
Re: Need help with data entry based on a button

@ DanteAmor,

That looks very interesting! I'd love some help creating that! I didn't realize that you could do that. Could that form work with automatically creating a new roll #? Thank you for the reply! Looks like exactly what I'm looking for! I'd still like to use the sheets, but as far as entering the data, that looks perfect! Great job.
 
Upvote 0
Re: Need help with data entry based on a button

I share an advance.

Code: 
Option Explicit
Dim shCat As Worksheet, shMain As Worksheet


Private Sub ComboBox1_Change()
  Dim init As String, i As Long, wmax As Long
  If ComboBox1.ListIndex > -1 Then
    wmax = 1
    init = shCat.Range("B" & ComboBox1.ListIndex + 2).Value
    For i = shMain.Range("D" & Rows.Count).End(xlUp).Row To 4 Step -1
      If Left(shMain.Cells(i, "D").Value, 2) = init Then
        wmax = Val(Mid(shMain.Cells(i, "D"), 3)) + 1
      End If
    Next
    LabelRoll.Caption = init & wmax
  End If
End Sub


Private Sub CommandButton1_Click()
  Dim lr As Long, i As Long
  If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then
    MsgBox "Fill combobox1"
    Exit Sub
  End If
  lr = shMain.Range("D" & Rows.Count).End(xlUp)(2).Row
  shMain.Range("C" & lr).Value = ComboBox1
  shMain.Range("D" & lr).Value = LabelRoll.Caption
  For i = Columns("E").Column To Columns("O").Column
    shMain.Cells(lr, i).Value = Me.Controls("TextBox" & i - 3)
  Next
  MsgBox "Data entered"
  ComboBox1.Value = ""
  LabelRoll.Caption = ""
  For i = 2 To 12
    Me.Controls("TextBox" & i).Value = ""
  Next
End Sub


Private Sub UserForm_Activate()
  Dim i As Long
  Set shMain = Sheets("Main")
  Set shCat = Sheets("Cat")
  For i = 2 To shCat.Range("A" & Rows.Count).End(xlUp).Row
    ComboBox1.AddItem shCat.Cells(i, "A").Value
  Next
End Sub

Try this file and tell me.

https://www.dropbox.com/s/8io3gf3cebx625u/Roll userform.xlsm?dl=0


If you have more catalogs, then you tell me which field is a catalog and I change the textbox to a combobox, as I did with the categories.
 
Upvote 0
Re: Need help with data entry based on a button

DanteAmor said:
I share an advance.

Code: 
Option Explicit
Dim shCat As Worksheet, shMain As Worksheet


Private Sub ComboBox1_Change()
  Dim init As String, i As Long, wmax As Long
  If ComboBox1.ListIndex > -1 Then
    wmax = 1
    init = shCat.Range("B" & ComboBox1.ListIndex + 2).Value
    For i = shMain.Range("D" & Rows.Count).End(xlUp).Row To 4 Step -1
      If Left(shMain.Cells(i, "D").Value, 2) = init Then
        wmax = Val(Mid(shMain.Cells(i, "D"), 3)) + 1
      End If
    Next
    LabelRoll.Caption = init & wmax
  End If
End Sub


Private Sub CommandButton1_Click()
  Dim lr As Long, i As Long
  If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then
    MsgBox "Fill combobox1"
    Exit Sub
  End If
  lr = shMain.Range("D" & Rows.Count).End(xlUp)(2).Row
  shMain.Range("C" & lr).Value = ComboBox1
  shMain.Range("D" & lr).Value = LabelRoll.Caption
  For i = Columns("E").Column To Columns("O").Column
    shMain.Cells(lr, i).Value = Me.Controls("TextBox" & i - 3)
  Next
  MsgBox "Data entered"
  ComboBox1.Value = ""
  LabelRoll.Caption = ""
  For i = 2 To 12
    Me.Controls("TextBox" & i).Value = ""
  Next
End Sub


Private Sub UserForm_Activate()
  Dim i As Long
  Set shMain = Sheets("Main")
  Set shCat = Sheets("Cat")
  For i = 2 To shCat.Range("A" & Rows.Count).End(xlUp).Row
    ComboBox1.AddItem shCat.Cells(i, "A").Value
  Next
End Sub

Try this file and tell me.

https://www.dropbox.com/s/8io3gf3cebx625u/Roll userform.xlsm?dl=0


If you have more catalogs, then you tell me which field is a catalog and I change the textbox to a combobox, as I did with the categories.
Click to expand...


This is great work! 100% fully functioning. This is SO close to being perfect. Thank you so much!! The ONLY thing left now, is to add a "DELETE ROLL" button. That way, the end user only has to type which roll he wants to delete. Is it possible to add that as well? I'd like the end user to be able to enter the roll # and have it delete the row of data as if it was never there. Once it's out of our inventory, we don't need to track it ever again.

Delete Roll Workflow:

* Click "Remove Roll"
* User types roll #
* Are you sure? Y/N
* Excel data sorts to clean up blank rows.


@ DanteAmor this is amazing. Thanks again for your help on this. I really appreciate it!

I shared the document I had created below. The only real difference is that the categories are on their own sheets. By chance would you be able to incorporate the code you wrote up in this sheet with the buttons I have?

https://www.mediafire.com/file/7zshsr6n937a4ee/Used_Belting_Manager.xlsm/file
 
Last edited:
Upvote 0
Re: Need help with data entry based on a button

Ok, it works as follows:


- You only need one button (to add or delete in any category)-.
- The categories remain on the "cat" sheet.
- The names of the sheets must be equal to the name of the sheets.
- Open the form.
- Select the category, 2 activities automatically happen:
* Introduces the following Roll number.
* In the Listbox control it shows you the current Roll.
- You can fill in the fields and add the new Roll.
- Or, you can select a Roll from the Listbox and press Delete.


The code is responsible for adding or deleting the Roll of the sheet according to the selected category.


- Select another category to see the Roll of another sheet.

Versión 1:

Code: 
Dim shCat As Worksheet, shMain As Worksheet


Private Sub ComboBox1_Change()
'SELECT CATEGORY
  Dim init As String, i As Long, wMax As Long, sh As Worksheet
  If ComboBox1.ListIndex > -1 Then
    Set sh = Sheets(ComboBox1.Value)
    wMax = 1
    init = shCat.Range("B" & ComboBox1.ListIndex + 2).Value
    ListBox1.Clear
    For i = 4 To sh.Range("D" & Rows.Count).End(xlUp).Row
      ListBox1.AddItem sh.Cells(i, "D").Value
      ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, "E").Value
      If Val(Mid(sh.Cells(i, "D"), 3)) > wMax Then
        wMax = Val(Mid(sh.Cells(i, "D"), 3))
      End If
    Next
    LabelRoll.Caption = init & wMax + 1
  End If
End Sub


Private Sub CommandButton1_Click()
'ADD BELT
  Dim lr As Long, i As Long, sh As Worksheet
  
  If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then
    MsgBox "Fill Origin"
    ComboBox1.SetFocus
    Exit Sub
  End If
  Set sh = Sheets(ComboBox1.Value)
  
  lr = sh.Range("D" & Rows.Count).End(xlUp)(2).Row
  sh.Range("C" & lr).Value = ComboBox1
  sh.Range("D" & lr).Value = LabelRoll.Caption
  For i = Columns("E").Column To Columns("O").Column
    sh.Cells(lr, i).Value = IIf(IsNumeric(Me.Controls("TextBox" & i - 3)), Val(Me.Controls("TextBox" & i - 3)), Me.Controls("TextBox" & i - 3))
  Next
  MsgBox "Data entered"
  ComboBox1.Value = ""
  LabelRoll.Caption = ""
  For i = 2 To 12
    Me.Controls("TextBox" & i).Value = ""
  Next
  ListBox1.Clear
End Sub


Private Sub CommandButton2_Click()
'DELETE BELT
  Dim sh As Worksheet, f As Range
  If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then
    MsgBox "Fill Origin"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If ListBox1.ListIndex = -1 Then
    MsgBox "Select Belt"
    ListBox1.SetFocus
    Exit Sub
  End If
  Set sh = Sheets(ComboBox1.Value)
  Set f = sh.Range("D:D").Find(ListBox1, , xlValues, xlWhole)
  If Not f Is Nothing Then
    If MsgBox("Are you sure", vbQuestion & vbYesNo, "DELETE ROLL") = vbYes Then
      sh.Rows(f.Row).Delete
      ListBox1.RemoveItem (ListBox1.ListIndex)
    End If
  End If


End Sub


Private Sub UserForm_Activate()
  Dim i As Long
  Set shMain = Sheets("Main")
  Set shCat = Sheets("Cat")
  For i = 2 To shCat.Range("A" & Rows.Count).End(xlUp).Row
    ComboBox1.AddItem shCat.Cells(i, "A").Value
  Next
End Sub

----------------------------------------------
The new Form

12252894ed6e6d5311aab2899684a665.jpg


-------------------------------------------------
The file:

https://www.dropbox.com/s/ebtsr4t9dbt39rl/Used Belting Manager v1.xlsm?dl=0
 
Upvote 0
Re: Need help with data entry based on a button

DanteAmor said:
Ok, it works as follows:


- You only need one button (to add or delete in any category)-.
- The categories remain on the "cat" sheet.
- The names of the sheets must be equal to the name of the sheets.
- Open the form.
- Select the category, 2 activities automatically happen:
* Introduces the following Roll number.
* In the Listbox control it shows you the current Roll.
- You can fill in the fields and add the new Roll.
- Or, you can select a Roll from the Listbox and press Delete.


The code is responsible for adding or deleting the Roll of the sheet according to the selected category.


- Select another category to see the Roll of another sheet.

Versión 1:

Code: 
Dim shCat As Worksheet, shMain As Worksheet


Private Sub ComboBox1_Change()
'SELECT CATEGORY
  Dim init As String, i As Long, wMax As Long, sh As Worksheet
  If ComboBox1.ListIndex > -1 Then
    Set sh = Sheets(ComboBox1.Value)
    wMax = 1
    init = shCat.Range("B" & ComboBox1.ListIndex + 2).Value
    ListBox1.Clear
    For i = 4 To sh.Range("D" & Rows.Count).End(xlUp).Row
      ListBox1.AddItem sh.Cells(i, "D").Value
      ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, "E").Value
      If Val(Mid(sh.Cells(i, "D"), 3)) > wMax Then
        wMax = Val(Mid(sh.Cells(i, "D"), 3))
      End If
    Next
    LabelRoll.Caption = init & wMax + 1
  End If
End Sub


Private Sub CommandButton1_Click()
'ADD BELT
  Dim lr As Long, i As Long, sh As Worksheet
  
  If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then
    MsgBox "Fill Origin"
    ComboBox1.SetFocus
    Exit Sub
  End If
  Set sh = Sheets(ComboBox1.Value)
  
  lr = sh.Range("D" & Rows.Count).End(xlUp)(2).Row
  sh.Range("C" & lr).Value = ComboBox1
  sh.Range("D" & lr).Value = LabelRoll.Caption
  For i = Columns("E").Column To Columns("O").Column
    sh.Cells(lr, i).Value = IIf(IsNumeric(Me.Controls("TextBox" & i - 3)), Val(Me.Controls("TextBox" & i - 3)), Me.Controls("TextBox" & i - 3))
  Next
  MsgBox "Data entered"
  ComboBox1.Value = ""
  LabelRoll.Caption = ""
  For i = 2 To 12
    Me.Controls("TextBox" & i).Value = ""
  Next
  ListBox1.Clear
End Sub


Private Sub CommandButton2_Click()
'DELETE BELT
  Dim sh As Worksheet, f As Range
  If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then
    MsgBox "Fill Origin"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If ListBox1.ListIndex = -1 Then
    MsgBox "Select Belt"
    ListBox1.SetFocus
    Exit Sub
  End If
  Set sh = Sheets(ComboBox1.Value)
  Set f = sh.Range("D:D").Find(ListBox1, , xlValues, xlWhole)
  If Not f Is Nothing Then
    If MsgBox("Are you sure", vbQuestion & vbYesNo, "DELETE ROLL") = vbYes Then
      sh.Rows(f.Row).Delete
      ListBox1.RemoveItem (ListBox1.ListIndex)
    End If
  End If


End Sub


Private Sub UserForm_Activate()
  Dim i As Long
  Set shMain = Sheets("Main")
  Set shCat = Sheets("Cat")
  For i = 2 To shCat.Range("A" & Rows.Count).End(xlUp).Row
    ComboBox1.AddItem shCat.Cells(i, "A").Value
  Next
End Sub

----------------------------------------------
The new Form

12252894ed6e6d5311aab2899684a665.jpg


-------------------------------------------------
The file:

https://www.dropbox.com/s/ebtsr4t9dbt39rl/Used Belting Manager v1.xlsm?dl=0
Click to expand...


...... You are amazing. This is absolutely incredible. Thank you SOO much for this! I can't thank you enough!

It's exactly what I was looking for. Amazing job!

I've gone through some of the code to try to understand it.. I definitely couldn't have done it on my own. I appreciate the help on this. Couldn't have done it without you.

I love the forums here and the people on them!
 
Upvote 0
Re: Need help with data entry based on a button

figuare9 said:
...... You are amazing. This is absolutely incredible. Thank you SOO much for this! I can't thank you enough!

It's exactly what I was looking for. Amazing job!

I've gone through some of the code to try to understand it.. I definitely couldn't have done it on my own. I appreciate the help on this. Couldn't have done it without you.

I love the forums here and the people on them!
Click to expand...

I'm glad to help you. I appreciate your kind comments.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
User form textbox to determine specific worksheet location for input data
Replies
1
Views
260
Skyybot
Skyybot
D
  • Question
VBA Loop Through Named Ranges
Replies
1
Views
106
Jeffrey Mahoney
Jeffrey Mahoney
G
  • Question
Trouble pre-setting userform values based off user input
Replies
2
Views
62
gc2001
G
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
132
bh24524
B
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
145
mikefisher
M

Forum statistics

Threads
1,214,518
Messages
6,119,996
Members
448,935
Latest member
ijat

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