Userform Question

  • Thread starter Thread starter K1KKKA
  • Start date Start date
K

K1KKKA

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
 
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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
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
 
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.


--
HTH

Bob Phillips

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

Fantastic, Works a dream for showing the details entered.

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

Steve
 
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

=================================================================
 
Sorry Steve, I am not understanding what you mean.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
What a Star, Works absolutely fine.

Many thanks BOB

Appreciate all your help :)




Steve


Bob Phillips wrote:
 
Back
Top