Data Entry Form

[Guest]

I am trying to develop a user form for updating some records. I would like
to be able to use a combo box to select a field in column A. Then have two
text boxes populated with the remaining record (i.e. column B & C). I have
the combo box working, but I’m having trouble figuring out how to index &
offset from the first column.

I know I could use Excel’s data form, but it doesn’t have a combo box and I
plan on building upon my form for other future use. Besides, it’s too much
fun trying to make my own : )

Thanks in advance,
Steve

 

[Incidental]

Hi Steve

You could try something like the code below that is triggered when you
change the value in the combobox. It will search column A for the
value of the combobox and activate that cell, then from there put the
corresponding data in the textboxes by using offset.

Option Explicit
Dim MyStr As String 'declare your variable

Private Sub ComboBox1_Change()

MyStr = ComboBox1.Value 'pass combobox value to a string

With [A:A] 'tell sub you want to run code in column A

.Find(What:=MyStr, LookAt:=xlPart,
SearchDirection:=xlNext).Activate
'above will find and activate the cell that matches the combobox1
value
TextBox1.Value = ActiveCell.Offset(0, 1)
'above will put the value in column b into the first textbox
TextBox2.Value = ActiveCell.Offset(0, 2)
'above will put the value in column c into the second textbox
End With

End Sub

Hope this helps

S

 

[Incidental]

hi again

i should have changed the search field when i sent the last post,
please ammend it to read

..Find(What:=MyStr, LookAt:=xlWhole, SearchDirection:=xlNext).Activate

searching for xlWhole will ensure you don't run into any problems with
entries that are similar

S

 

[Dave Peterson]

Your combobox can have more than one column in it (and you can hide as many
columns as you want!).

So once you fill the combobox with the values, you can use the combobox's list
to get the associated values without going back to the worksheet.

I put a commandbutton, 2 textboxes, and a combobox on a userform. This was the
code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()

With Me.ComboBox1
If .ListIndex < 0 Then
'do nothing
Else
Me.TextBox1.Value = .List(.ListIndex, 1)
Me.TextBox2.Value = .List(.ListIndex, 2)
End If
End With
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With Me.ComboBox1
.ColumnCount = 3
.ColumnWidths = "33;0;0"
.RowSource = myRng.Address(external:=True)
End With

Me.ComboBox1.Style = fmStyleDropDownList

End Sub

 

[Guest]

Thanks so much.

I wrestled with this one for a couple of days, and now I’m faced with two
options. I plan on adding up to twenty columns, and organizing the form with
some multipages. Both approaches will work for this, but for my own
edification, what are the pros and cons of the two?

Thanks again,
Steve

 

[Dave Peterson]

If you have duplicates in the key column, it may make it difficult to make sure
you're on the right one.

Personally, I like the multicolumn combobox. It seems easier to me. But your
mileage may vary.

 

[Guest]

Dave,

Ok. Thanks again for your help.

If you have duplicates in the key column, it may make it difficult to make sure
you're on the right one.

Personally, I like the multicolumn combobox. It seems easier to me. But your
mileage may vary.

 

[Guest]

Dave,
I used your method for loading the form, but when I used the following
process, to make changes to the record, the values in the combo box columns
refill the text boxes with the origianl values, when Apply is clicked. Do
you have a way around this?

Thanks,
Steve

Private Sub Button_Apply_Click()
Dim Mystring As String
Mystring = cboProject.Value
With Worksheets("Database").Range("A2")
If cboProject.Value = "" Then
Else
.Find(What:=Mystring, LookAt:=xlWhole,
SearchDirection:=xlNext).Activate
ActiveCell.Offset(0, 1) = Me.txt1.Value
ActiveCell.Offset(0, 2) = Me.txt2.Value
End If
End With
End Sub

 

[Dave Peterson]

Maybe you could use something like:

Option Explicit
Dim myRng As Range
Dim BlkProc As Boolean
Private Sub cboProject_Change()
If BlkProc = True Then Exit Sub
With Me.cboProject
If .ListIndex < 0 Then
'do nothing
Else
Me.txt1.Value = .List(.ListIndex, 1)
Me.txt2.Value = .List(.ListIndex, 2)
End If
End With
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub Button_Apply_Click()
If Me.cboProject.ListIndex = -1 Then
Beep
Else
BlkProc = True
'come down to that row (same column)
With myRng.Cells(1).Offset(Me.cboProject.ListIndex, 0)
'move over 1 column
.Offset(0, 1).Value = Me.txt1.Value
'move over 2 columns
.Offset(0, 2).Value = Me.txt2.Value
End With
BlkProc = False
End If
End Sub
Private Sub UserForm_Initialize()
With Worksheets("database")
Set myRng = .Range("a1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With Me.cboProject
.ColumnCount = 3
.ColumnWidths = "33;0;0"
.RowSource = myRng.Address(external:=True)
End With
Me.cboProject.Style = fmStyleDropDownList
End Sub

The blkProc stuff works a little like application.enableevents. But the
programmer has to do the checking. So anything that your code does that causes
something to fire (that you don't want fired) can be blocked by toggling a
variable and then checking that variable.

 

[Guest]

Dave,
I put your code to use and it has been working very well. I have been so
busy lately that I haven’t taken the time to come back and say thanks.

If you’re ever in Fort Worth, TX and in need of a steak dinner let me know!

Thanks again,
Steve

 

[Dave Peterson]

Glad you got it working!

And you have steaks in Texas? Who'da thunk it????