Solved

Excel -- Data entry form

Posted on 2014-04-21
25
386 Views
Last Modified: 2014-04-25
Experts:

I need some advice/recommendations for tweaking/streamlining a data entry form.

First, please see attached (working) data entry form.   Allow me to preference that this particular version is generic... the actual data entry form will contain additional fields.

Anyhow, here's the current process:
- User opens XLS and selects values from the 3 drop-down boxes.
- Upon clicking "Ok", the previously selected values are inserted in cell range B1:D1.
- I've labeled the drop-down row as "Scenario #1"

Ok, here's where I need some help/ideas:
- Upon opening the form, the user will select "# of scenarios" from a drop-down menu.   The value for # of scenarios might range up to 10 scenarios.. or possibly even beyond 10.
- For sake of argument, let's say that the user selects a scenario value = 6.
- If so, the user then must be able to enter 6 values for each of the 3 fields/drop-downs.
- Consequently, the worksheet will store data from B1:D6.

Now, I guess I could just add drop-downs for up to 10 scenarios.   Problem is though I have more than just 3 fields for each scenario.   Thus, the pop-up form could get quite cluttered.

Also, what if user selected "15 scenarios"... so, you can see that this approach may not be the best option.    

Next, it was also requested to use Excel (vs. Access where this could be more easily accomplished).

Given the Excel constraints, does anyone know of a somewhat "dynamic" solution for this particular method.    I'm open to ideas (would love to see an actual solution in XLS).

Thank you in advance,
EEH
Data-Entry-Form.xlsm
0
Comment
Question by:ExpExchHelp
  • 11
  • 10
  • 4
25 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40013581
You could allow the user to use the same drop-downs and each time the OK button is pressed the current values in those drop-downs are stored in new rows of the spreadsheet.  So on their 6th scenario entry, row 6 will receive the values (rows 1-5 having already been populated the same way).
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40013583
You could have the users enter the data in one textbox delimited by a character of your choice (new line..), from there it is easy to split the data into an array (using the split function), from an array it's a piece of cake to write to a worksheet.
0
 

Author Comment

by:ExpExchHelp
ID: 40013587
David Hansen (sl8rz) -- that might be a doable solution.    Not sure how to code it though.   Could you please provide an example?

MacroShadow -- same as previous comment.   I don't consider myself a developer.   I'd welcome if you could provide a basic example in the XLS.

Thank you in advance,
EEH
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40013751
Ok check it out. Enter the data like this:

TestData1 TestData1 TestData1
TestData2 TestData2 TestData2
TestData3 TestData3 TestData3
TestData4 TestData4 TestData4
TestData5 TestData5 TestData5
TestData6 TestData6 TestData6
Data-Entry-Form.xlsm
0
 

Author Comment

by:ExpExchHelp
ID: 40013770
MacroShadow:

Thank you for the proposed method... I appreciate it.

I will admit, it took me 3 attempts to have the data accepted (i.e., not getting an error).

While the form did enter the data, the values for the 3 fields are very specific.   That is, some will include numeric values while other may even include strings.    

Thus, I can see that users will not enter the correct information.

Again, I appreciate your proposed form but I'm not too sure if users will be able to enter data w/o incurring/making errors.

Is there any way the original method (i.e., using drop-downs) can be integrated into your proposed solution?

Thanks,
EEH
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40013815
The only possibility I can think of is using the following steps:
1. user chooses the scenario#
2. the user enters the data for the first row
3. the data is written to the worksheet
4. the user is prompted to enter the data for the second row
5. the data is written to the worksheet
6. the user is prompted to enter the data for the third row
etc. etc. ...
0
 

Author Comment

by:ExpExchHelp
ID: 40013831
MacroShadow:

Yes, I think that'll work.    Any idea how to accomplish that?

EEH
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40013864
0
 

Author Comment

by:ExpExchHelp
ID: 40014530
MacroShadow:

Getting very close... I'm not sure if I entirely following current process.

Here's what see when completing the spreadsheet:
- Open up form
- Select "3" from # of scenarios
- Select any value from the three fields
- Click Ok.
- Now, I'm getting a dialogue box indicating to complete data for row # 1 (1st values are dumped into 2nd row)
- I click Ok (in hopes to be able to add data for next row).   Instead though, I get 2nd dialogue box asking to enter data for "row".   When I click Ok, I get yet another dialogue box.

Overall, the popping up of dialogue boxes is good.   However, I didn't have the opportunity to enter data in between.

In summary, the process would work excellent if the following occurs.
- User selects "3 scenarios".
- User enters values for 1st scenario.   Clicks Ok.
- User then is reminded to enter value for 2nd scenario.   Click Ok.
- User enters values for 3rd scenario.   Clicks Ok.
- Then values are dumped into spreadsheet.

Again, thank you for your help in this matter.

EEH
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40015431
This works perfectly for me:
Add 3 ActiveX comboboxes and one button to your sheet (I used "Sheet1") then use the following code:
Sub Button4_Click()
'
' Button4_Click Macro
'
     Dim ws As Worksheet
     Dim cmbo1 As ComboBox
     Dim cmbo2 As ComboBox
     Dim cmbo3 As ComboBox
     
     Set ws = Worksheets("Sheet1")
     Set cmbo1 = ws.OLEObjects("Combobox1").Object
     Set cmbo2 = ws.OLEObjects("Combobox2").Object
     Set cmbo3 = ws.OLEObjects("Combobox3").Object

     Dim i As Integer
          
     For i = 1 To 6
          Dim myCell As String
          myCell = "A" & CStr(i)
          If Len(Cells(i, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell).Select
               ActiveCell.Value = cmbo1.Value & ", " & cmbo2.Value & ", " & cmbo3.Value
               Exit For
          End If
     Next
End Sub

Open in new window

By the way, if you need help with populating your comboboxes, I have code for that too.
0
 

Author Comment

by:ExpExchHelp
ID: 40015513
David Hansen (sl8rz):

Thanks -- would it be possible for you for send me the working XLS?

EEH
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40015542
Sure.  Just a sec.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40015569
Here you go...(By the way, you'll see in the module's code that each time the workbook is opened, the text in column A is deleted).  You may want to change that if the data is meant to persist.
Test7.xlsm
0
 

Author Comment

by:ExpExchHelp
ID: 40015830
David Hansen (sl8rz):

Thank you... I appreciate it.

Two things which would make it easier.

1.  Is it doable to have the values added into separate columns (vs. being comma separated)?

2. Doable to have the drop-down menus being populated from look up tables?

EEH
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40016118
Sure.  Look at the code that I've posted.  In the FOR LOOP you'll just need three "myCell" variables (I'd suggest myCell_A, myCell_B, and myCell_C).  Those will be declared and set in the same places and in the same way as you see already (ie.   myCell_A = "A" & CStr(i), myCell_B = "B" & Cstr(i), etc.   ).

As far as using the named ranges to fill the comboboxes use this below (I created a named range of "States" just for testing).
.ComboBox1.List = Application.Transpose(Range("States"))
0
 

Author Comment

by:ExpExchHelp
ID: 40016386
Hmh... I see.

Trying to figure out how to modify the IF Else statement... getting some errors.

EEH
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40018445
You may want to just replicate the IF..ELSE statement two more times so that it does what it currently is doing but only for choice A.  Then, in the same subroutine have it continue on to the next IF..Else statement for B and then one more for C.  I suppose you can put all the logic in one IF statement, but I think it is easier to just break it out into three IF's (one after another).
0
 

Author Comment

by:ExpExchHelp
ID: 40019670
David:

I've tried that... first value for column A was placed (correctly) into A1; however, first value for column B was placed (incorrectly) into B2... vs. B1.

Not sure what's missing... suggestions for changing the routine?

Also, I have header row... so, I actually need to have values placed starting in row 2.

EEH
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40020231
Keep all three IF's inside one For-Loop; if you do this the "i" (which denotes row number) will remain the same for each column.  The looping only exists to find the next empty row....if we only needed one row (which we don't), for one set of choices, then we wouldn't need any For-Loop at all.  We'd just use row 1.  If this doesn't get you unstuck, post your code and I'll point-out exactly what your need to change.
0
 

Author Comment

by:ExpExchHelp
ID: 40021245
David:

See code below... suggestions how to fix it that all 3 drop-downs start dumping into row #2 first?

EEH



Sub Button4_Click()
'
' Button4_Click Macro
'
     Dim ws As Worksheet
     Dim cmbo1 As ComboBox
     Dim cmbo2 As ComboBox
     Dim cmbo3 As ComboBox
     
     Set ws = Worksheets("Sheet1")
     Set cmbo1 = ws.OLEObjects("Combobox1").Object
     Set cmbo2 = ws.OLEObjects("Combobox2").Object
     Set cmbo3 = ws.OLEObjects("Combobox3").Object

     Dim i As Integer
          
     For i = 1 To 6
          Dim myCell_A As String
          myCell_A = "A" & CStr(i)
          If Len(Cells(i, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_A).Select
               ActiveCell.Value = cmbo1.Value
               Exit For
          End If
     Next
     
     
      Dim j As Integer
          
     For j = 1 To 6
          Dim myCell_B As String
          myCell_B = "B" & CStr(j)
          If Len(Cells(j, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_B).Select
               ActiveCell.Value = cmbo2.Value
               Exit For
          End If
     Next
     
     
     
     
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40021403
I'll refer to the line numbers in your code above.

You just want one For-loop, to that end we will remove lines 27, 30 and 32.  Then replace "j" with "i".  Also, since you'd like the results to begin posting in row 2 of your spreadsheet (instead on row 1) we will alter line 17 to read:
For i = 2 To 7

Open in new window

Note: you don't have to stop at row 7.  Use as many rows as you want by altering "i = 2 To whatever".
0
 

Author Comment

by:ExpExchHelp
ID: 40021597
David:

I appreciate your patience... still, it's not working.   See code below.

Would it be possible for your to post the XLS since you know exactly what needs to be fixed?

EEH


Sub Button4_Click()
'
' Button4_Click Macro
'
     Dim ws As Worksheet
     Dim cmbo1 As ComboBox
     Dim cmbo2 As ComboBox
     Dim cmbo3 As ComboBox
     
     Set ws = Worksheets("Sheet1")
     Set cmbo1 = ws.OLEObjects("Combobox1").Object
     Set cmbo2 = ws.OLEObjects("Combobox2").Object
     Set cmbo3 = ws.OLEObjects("Combobox3").Object

     Dim i As Integer
          
     For i = 2 To 7
          Dim myCell_A As String
          myCell_A = "A" & CStr(i)
          If Len(Cells(i, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_A).Select
               ActiveCell.Value = cmbo1.Value
               Exit For
          End If
     
      
          Dim myCell_B As String
          myCell_B = "B" & CStr(i)
          If Len(Cells(i, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_B).Select
               ActiveCell.Value = cmbo2.Value
               Exit For
          End If
          
          Dim myCell_C As String
          myCell_C = "C" & CStr(i)
          If Len(Cells(i, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_C).Select
               ActiveCell.Value = cmbo3.Value
               Exit For
          End If
     
          
          
     Next
     
     
End Sub

Open in new window

0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 40022822
The "Exit For" was kicking us out of the loop before the "B" and "C" IF's could run.  So I replaced them with a boolean flag...take a look.
Sub Button4_Click()
'
' Button4_Click Macro
'
     Dim ws As Worksheet
     Dim cmbo1 As ComboBox
     Dim cmbo2 As ComboBox
     Dim cmbo3 As ComboBox
     Dim foundEmptyRow As Boolean
     
     Set ws = Worksheets("Sheet1")
     Set cmbo1 = ws.OLEObjects("Combobox1").Object
     Set cmbo2 = ws.OLEObjects("Combobox2").Object
     Set cmbo3 = ws.OLEObjects("Combobox3").Object

     Dim i As Integer
     
     foundEmptyRow = False
     For i = 2 To 7
          Dim myCell_A As String
          myCell_A = "A" & CStr(i)
          If Len(Cells(i, 1)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_A).Select
               ActiveCell.Value = cmbo1.Value
               foundEmptyRow = True
          End If
     
      
          Dim myCell_B As String
          myCell_B = "B" & CStr(i)
          If Len(Cells(i, 2)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_B).Select
               ActiveCell.Value = cmbo2.Value
               foundEmptyRow = True
          End If
          
          Dim myCell_C As String
          myCell_C = "C" & CStr(i)
          If Len(Cells(i, 3)) > 0 Then
               'skip to the next row (next loop)
          Else
               Range(myCell_C).Select
               ActiveCell.Value = cmbo3.Value
               foundEmptyRow = True
          End If
          
          If foundEmptyRow = True Then
               Exit For
          End If
     Next
End Sub

Open in new window

Test7.xlsm
0
 

Author Closing Comment

by:ExpExchHelp
ID: 40023839
David Hansen (sl8rz):

That's an impressive solution!!!    It works brilliant.  

I like the idea to clear the values upon opening.   Also, I like the two options for either hard-coding the values into the combo drop-down or using the lookup/name range.

Again, thank you so much for this excellent solution.  ;)

EEH
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40023907
You are very welcome :)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now