Code Review

[Eric]

Hello all, I got some fantastic help on this problem yesterday and wanted to
see if someone could take a look at my code and possibly offer some
suggestions.

What I have is a sheet that users access to pull up customer information
from another sheet. This code is attached to a form with a ComboBox
(ComboBox1) and 3 TextBoxes (1,2,3) that will eventually allow the users to
locate and change customer info. This code locates and fills in the
TextBoxes' information based upon the record displayed in The ComboBox. I can
change the information in the TextBoxes, but the new info added right now
only changes for TextBox1. Once I place the focus back onto ComboBox1, the
information reverts back to what was originally there. Now that I've confused
everyone, here's the code. TextBoxes 2 & 3 are not updating the sheet with
the new info. Any Thoughts?

Private Sub ComboBox1_Change()

Dim res As Variant
Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
Me.Controls("TextBox" & i) = .Cells(res, i + 1)
Next i
End If
End With
End Sub

Private Sub CommandButton1_Click()

Dim res As Variant
Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
If Me.Controls("TextBox" & i) <> "" Then .Cells(res, i + 1) =
Me.Controls("TextBox" & i).Value
Next i
End If
End With
End Sub

Any help would be appreciated! Thanks!!

[Toppers]

Eric,
I tried the code and it worked OK. Check the "i" in the code is
not a 1 although your posted is correct so this unlikely.

if you want post your spreadsheet to me (toppers@johntopley.fsnet.co.uk)

"Eric" wrote:

> Hello all, I got some fantastic help on this problem yesterday and wanted to
> see if someone could take a look at my code and possibly offer some
> suggestions.
>
> What I have is a sheet that users access to pull up customer information
> from another sheet. This code is attached to a form with a ComboBox
> (ComboBox1) and 3 TextBoxes (1,2,3) that will eventually allow the users to
> locate and change customer info. This code locates and fills in the
> TextBoxes' information based upon the record displayed in The ComboBox. I can
> change the information in the TextBoxes, but the new info added right now
> only changes for TextBox1. Once I place the focus back onto ComboBox1, the
> information reverts back to what was originally there. Now that I've confused
> everyone, here's the code. TextBoxes 2 & 3 are not updating the sheet with
> the new info. Any Thoughts?
>
> Private Sub ComboBox1_Change()
>
> Dim res As Variant
> Set ws1 = Worksheets("Customer Database")
> ws1.Activate
> With ws1
> res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
> If IsError(res) Then
> MsgBox ComboBox1.Value & " Not Found"
> Else
> For i = 1 To 3
> Me.Controls("TextBox" & i) = .Cells(res, i + 1)
> Next i
> End If
> End With
> End Sub
>
> Private Sub CommandButton1_Click()
>
> Dim res As Variant
> Set ws1 = Worksheets("Customer Database")
> ws1.Activate
> With ws1
> res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
> If IsError(res) Then
> MsgBox ComboBox1.Value & " Not Found"
> Else
> For i = 1 To 3
> If Me.Controls("TextBox" & i) <> "" Then .Cells(res, i + 1) =
> Me.Controls("TextBox" & i).Value
> Next i
> End If
> End With
> End Sub
>
> Any help would be appreciated! Thanks!!

[Eric]

Well, I checked the code and indeed the "i"s are "i"s. Something interesting
I found out. If I blank out the TextBoxes and only update one TextBox at a
time, this code as it is written works. The problem is, I'd like to be able
to make the code fill all 3 fields based upon info in the TextBoxes with one
click of a button. Not one at a time.

Any suggestions?

"Toppers" wrote:

> Eric,
> I tried the code and it worked OK. Check the "i" in the code is
> not a 1 although your posted is correct so this unlikely.
>
> if you want post your spreadsheet to me (toppers@johntopley.fsnet.co.uk)
>
> "Eric" wrote:
>
> > Hello all, I got some fantastic help on this problem yesterday and wanted to
> > see if someone could take a look at my code and possibly offer some
> > suggestions.
> >
> > What I have is a sheet that users access to pull up customer information
> > from another sheet. This code is attached to a form with a ComboBox
> > (ComboBox1) and 3 TextBoxes (1,2,3) that will eventually allow the users to
> > locate and change customer info. This code locates and fills in the
> > TextBoxes' information based upon the record displayed in The ComboBox. I can
> > change the information in the TextBoxes, but the new info added right now
> > only changes for TextBox1. Once I place the focus back onto ComboBox1, the
> > information reverts back to what was originally there. Now that I've confused
> > everyone, here's the code. TextBoxes 2 & 3 are not updating the sheet with
> > the new info. Any Thoughts?
> >
> > Private Sub ComboBox1_Change()
> >
> > Dim res As Variant
> > Set ws1 = Worksheets("Customer Database")
> > ws1.Activate
> > With ws1
> > res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
> > If IsError(res) Then
> > MsgBox ComboBox1.Value & " Not Found"
> > Else
> > For i = 1 To 3
> > Me.Controls("TextBox" & i) = .Cells(res, i + 1)
> > Next i
> > End If
> > End With
> > End Sub
> >
> > Private Sub CommandButton1_Click()
> >
> > Dim res As Variant
> > Set ws1 = Worksheets("Customer Database")
> > ws1.Activate
> > With ws1
> > res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
> > If IsError(res) Then
> > MsgBox ComboBox1.Value & " Not Found"
> > Else
> > For i = 1 To 3
> > If Me.Controls("TextBox" & i) <> "" Then .Cells(res, i + 1) =
> > Me.Controls("TextBox" & i).Value
> > Next i
> > End If
> > End With
> > End Sub
> >
> > Any help would be appreciated! Thanks!!

[Toppers]

Eric,
I changed all textboxes at once and it worked; I changed one
textbox and it worked. There is no need to blank out the textboxes - just
change the one(s) as required and leave the other(s) unchanged. Post your
book to me at address in previous posting , with some test data and I'll look
at this weekend.

"Eric" wrote:

> Well, I checked the code and indeed the "i"s are "i"s. Something interesting
> I found out. If I blank out the TextBoxes and only update one TextBox at a
> time, this code as it is written works. The problem is, I'd like to be able
> to make the code fill all 3 fields based upon info in the TextBoxes with one
> click of a button. Not one at a time.
>
> Any suggestions?
>
> "Toppers" wrote:
>
> > Eric,
> > I tried the code and it worked OK. Check the "i" in the code is
> > not a 1 although your posted is correct so this unlikely.
> >
> > if you want post your spreadsheet to me (toppers@johntopley.fsnet.co.uk)
> >
> > "Eric" wrote:
> >
> > > Hello all, I got some fantastic help on this problem yesterday and wanted to
> > > see if someone could take a look at my code and possibly offer some
> > > suggestions.
> > >
> > > What I have is a sheet that users access to pull up customer information
> > > from another sheet. This code is attached to a form with a ComboBox
> > > (ComboBox1) and 3 TextBoxes (1,2,3) that will eventually allow the users to
> > > locate and change customer info. This code locates and fills in the
> > > TextBoxes' information based upon the record displayed in The ComboBox. I can
> > > change the information in the TextBoxes, but the new info added right now
> > > only changes for TextBox1. Once I place the focus back onto ComboBox1, the
> > > information reverts back to what was originally there. Now that I've confused
> > > everyone, here's the code. TextBoxes 2 & 3 are not updating the sheet with
> > > the new info. Any Thoughts?
> > >
> > > Private Sub ComboBox1_Change()
> > >
> > > Dim res As Variant
> > > Set ws1 = Worksheets("Customer Database")
> > > ws1.Activate
> > > With ws1
> > > res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
> > > If IsError(res) Then
> > > MsgBox ComboBox1.Value & " Not Found"
> > > Else
> > > For i = 1 To 3
> > > Me.Controls("TextBox" & i) = .Cells(res, i + 1)
> > > Next i
> > > End If
> > > End With
> > > End Sub
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > Dim res As Variant
> > > Set ws1 = Worksheets("Customer Database")
> > > ws1.Activate
> > > With ws1
> > > res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
> > > If IsError(res) Then
> > > MsgBox ComboBox1.Value & " Not Found"
> > > Else
> > > For i = 1 To 3
> > > If Me.Controls("TextBox" & i) <> "" Then .Cells(res, i + 1) =
> > > Me.Controls("TextBox" & i).Value
> > > Next i
> > > End If
> > > End With
> > > End Sub
> > >
> > > Any help would be appreciated! Thanks!!