Userform Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

K1KKKA - 15 Jan 2007 11:22 GMT

Hi, i have posted a few times here and have been helped immensley, but
again i find myself a little stuck on a project.

I am using a userform to input data into the next available row, this
avoids risk to formulas that have been placed onto the worksheet,
(stops those that dont understand the fragility of them)

is there any VB code that would allow me to use a userform and recall
the information via a supplier name in column c and edit the data in
column E, J, H

I have been very succesful in playing with code to get the input side
working, but am having no luck with this section????

Any help

Steve

Reply to this Message

Bob Phillips - 15 Jan 2007 12:12 GMT

Assuming a form with a combobox for the supplier names, and 3 textboxes

Private Sub ComboBox1_Change()
Dim iRow As Long

With Me
iRow = .ComboBox1.ListIndex + 2
.TextBox1.Text = Worksheets("Sheet1").Cells(iRow, "E").Value
.TextBox2.Text = Worksheets("Sheet1").Cells(iRow, "J").Value
.TextBox3.Text = Worksheets("Sheet1").Cells(iRow, "H").Value
End With
End Sub

Private Sub UserForm_Activate()
Dim iLastRow As Long
Dim i As Long

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Me.ComboBox1.Clear
For i = 2 To iLastRow
Me.ComboBox1.AddItem .Cells(i, "C").Value
Next i
End With
End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi, i have posted a few times here and have been helped immensley, but
> again i find myself a little stuck on a project.
[quoted text clipped - 13 lines]
>
> Steve

Reply to this Message

K1KKKA - 15 Jan 2007 13:13 GMT

Bob,

Thanks Very much for the VB,

Just a small question, with regards the ListIndex, this would be the
named range for "C" i presume?

Steve

> Assuming a form with a combobox for the supplier names, and 3 textboxes
>
[quoted text clipped - 46 lines]
> >
> > Steve

Reply to this Message

K1KKKA - 15 Jan 2007 13:16 GMT

Last thing,

The information i want to Edit starts from Row 11, would this need to
show in the VB for this to work correctly

I have Date and headers, and other such info in the 1st 10 rows.

Steve

> Bob,
>
[quoted text clipped - 55 lines]
> > >
> > > Steve

Reply to this Message

Bob Phillips - 15 Jan 2007 14:25 GMT

I had assumed 1 header, if 10, then change

For i = 2 To iLastRow

to

For i = 11 To iLastRow

and this line

iRow = .ComboBox1.ListIndex + 2

to

iRow = .ComboBox1.ListIndex + 12

Listindex i s pointer to the item seleceted in the combobox.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Last thing,
>
[quoted text clipped - 64 lines]
> > > >
> > > > Steve

Reply to this Message

K1KKKA - 15 Jan 2007 14:42 GMT

Bob,

Fantastic, Works a dream for showing the details entered.

Should the code supplied, allow the editing of entered data and
replacing?

Steve

> I had assumed 1 header, if 10, then change
>
[quoted text clipped - 94 lines]
> > > > >
> > > > > Steve

Reply to this Message

K1KKKA - 15 Jan 2007 15:29 GMT

Have also noticed this displays data from the first line only, where as
i would like to match the cells to the supplier name, should there be a
"Match" statement in the VB?

Private Sub ComboBox1_Change()
Dim iRow As Long

With Me
iRow = .ComboBox1.ListIndex + 2
.TextBox1.Text = Worksheets("Mon").Cells(iRow, "E").Value
.TextBox2.Text = Worksheets("Mon").Cells(iRow, "J").Value
.TextBox3.Text = Worksheets("Mon").Cells(iRow, "H").Value
End With
End Sub

Private Sub UserForm_Activate()
Dim iLastRow As Long
Dim i As Long

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Me.ComboBox1.Clear
For i = 2 To iLastRow
Me.ComboBox1.AddItem .Cells(i, "C").Value
Next i
End With
End Sub

HYCH

Steve

=================================================================

> Bob,
>
[quoted text clipped - 103 lines]
> > > > > >
> > > > > > Steve

Reply to this Message

Bob Phillips - 15 Jan 2007 15:43 GMT

Sorry Steve, I am not understanding what you mean.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Have also noticed this displays data from the first line only, where as
> i would like to match the cells to the supplier name, should there be a
[quoted text clipped - 137 lines]
> > > > > > >
> > > > > > > Steve

Reply to this Message

K1KKKA - 15 Jan 2007 16:03 GMT

Bob,

I am interested in recalling the data by supplier and editing the
figures entered, but all i seem to be doing is calling the data for a
supplier.

i have tried to adjust what you gave me to look at the data & Edit, but
without any luck
I have daily sheets for which i would like people to enter a customer
name and details of goods received, but do not want them to access the
sheet itself, which is why i am using forms.

on occassions there are mistakes in the data, what i am looking for is
a way to edit the data from a userform.

Hope you can help

Steve

> Sorry Steve, I am not understanding what you mean.
>
[quoted text clipped - 157 lines]
> > > > > > > >
> > > > > > > > Steve

Reply to this Message

Bob Phillips - 15 Jan 2007 16:12 GMT

Okay, I think I see what you mean.

Add a button to the form and then use this version of the code

Private Sub CommandButton1_Click()
Dim iRow As Long

With Me
iRow = .ComboBox1.ListIndex + 12
Worksheets("Sheet1").Cells(iRow, "E").Value = .TextBox1.Text
Worksheets("Sheet1").Cells(iRow, "J").Value = .TextBox2.Text
Worksheets("Sheet1").Cells(iRow, "H").Value = .TextBox3.Text
End With
End Sub

Private Sub ComboBox1_Change()
Dim iRow As Long

With Me
iRow = .ComboBox1.ListIndex + 12
.TextBox1.Text = Worksheets("Sheet1").Cells(iRow, "E").Value
.TextBox2.Text = Worksheets("Sheet1").Cells(iRow, "J").Value
.TextBox3.Text = Worksheets("Sheet1").Cells(iRow, "H").Value
End With
End Sub

Private Sub UserForm_Activate()
Dim iLastRow As Long
Dim i As Long

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Me.ComboBox1.Clear
For i = 11 To iLastRow
Me.ComboBox1.AddItem .Cells(i, "C").Value
Next i
End With
End Sub

This will allow you to edit the data retrieved, and when the button is
clicked, write it back to where it came from

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Bob,
>
[quoted text clipped - 176 lines]
> > > > > > > > >
> > > > > > > > > Steve

Reply to this Message

K1KKKA - 15 Jan 2007 16:50 GMT

What a Star, Works absolutely fine.

Many thanks BOB

Appreciate all your help :)

Steve

> Okay, I think I see what you mean.
>
[quoted text clipped - 233 lines]
> > > > > > > > > >
> > > > > > > > > > Steve

Reply to this Message

Rate this thread: