Userform Help Please
 

Hope you guys can help, whilst I try I have to admit to not being very good
at this...

I have a sheet with rows of data. The unique identifier (UI) is in column
B. By using a userform I would like to insert data in in the row belonging
to the UI.

On the useform, as the UI exisits I assume I need to use a combo box so the
user can select the UI. I then want them to enter a number of answers in
boxes to provide the data, and for this I can use text box's? So far so
good. What I seem to be missing is how do I get the data entered to go to
the correct row and cells?

Thanks in advance

Userform Help Please
 

Since it's a unique ID, you can use Excel's =Match() to find the correct row.

Dim res as variant
dim UIDRng as range
dim UIDWks as worksheet

set uidwks = worksheets("sheetwithuniqueidinit")

with wks
set uidrng = .range("B:b")
end with


'not sure if you can have nothing in your combobox
if me.combobox1.value = "" then
beep
exit sub
end if

'check for a text match (that's what the combobox holds
res = application.match(me.combobox1.value, uidrng,0)
if iserror(res) then
'no match, check for a match for numbers
if isnumeric(me.combobox1.value) then
res = application.match(val(me.combobox1.value), uidrng, 0)
end if
end if

'is it still an error
if iserror(res) then
'no match for either!
msgbox "no match" 'Should this happen???
exit sub
end if

uidwks.cells(res,"A").value = "this is the value that goes in column A"
uidwks.cells(res,"Z").value = "New value in column Z
....

All untested, uncompiled. Watch for typos!




Topher wrote:

Hope you guys can help, whilst I try I have to admit to not being very good
at this...

I have a sheet with rows of data. The unique identifier (UI) is in column
B. By using a userform I would like to insert data in in the row belonging
to the UI.

On the useform, as the UI exisits I assume I need to use a combo box so the
user can select the UI. I then want them to enter a number of answers in
boxes to provide the data, and for this I can use text box's? So far so
good. What I seem to be missing is how do I get the data entered to go to
the correct row and cells?

Thanks in advance

--

Dave Peterson

Userform Help Please
 

Dave, thanks for your time to help but it does not seem to find the 'match'
as it places the data in the right column but not the right row. Could be me
though, my code is:

Private Sub Enter_Click()


Dim JOBName As Variant
Dim UIDRng As Range
Dim UIDWks As Worksheet

Set UIDWks = Worksheets("Ops Schedule")

With wks
Set UIDRng = Range("ABC")
End With


'not sure if you can have nothing in your combobox
If Me.Job_List1.Value = "" Then
Beep
Exit Sub
End If

'check for a text match (that's what the combobox holds
JOBName = Application.Match(Me.Job_List1.Value, UIDRng, 0)
If IsError(JOBName) Then
'no match, check for a match for numbers
If IsNumeric(Me.Job_List1.Value) Then
JOBName = Application.Match(Val(Me.Job_List1.Value), UIDRng, 0)
End If
End If

'is it still an error
If IsError(JOBName) Then
'no match for either!
MsgBox "no match" 'Should this happen???
Exit Sub
End If

Call Unprotect 'unprotects the sheet

UIDWks.Cells(JOBName, "FC").Value = IF_Qty.text


Call Protect 'Protects the sheet

End Sub



Private Sub UserForm_Activate()


Call Project_Name_Range 'Names the range "ABC"
Call Sort_Project_Alfa 'Does an alfabetic sort

End Sub

"Dave Peterson" wrote:

Since it's a unique ID, you can use Excel's =Match() to find the correct row.

Dim res as variant
dim UIDRng as range
dim UIDWks as worksheet

set uidwks = worksheets("sheetwithuniqueidinit")

with wks
set uidrng = .range("B:b")
end with


'not sure if you can have nothing in your combobox
if me.combobox1.value = "" then
beep
exit sub
end if

'check for a text match (that's what the combobox holds
res = application.match(me.combobox1.value, uidrng,0)
if iserror(res) then
'no match, check for a match for numbers
if isnumeric(me.combobox1.value) then
res = application.match(val(me.combobox1.value), uidrng, 0)
end if
end if

'is it still an error
if iserror(res) then
'no match for either!
msgbox "no match" 'Should this happen???
exit sub
end if

uidwks.cells(res,"A").value = "this is the value that goes in column A"
uidwks.cells(res,"Z").value = "New value in column Z
....

All untested, uncompiled. Watch for typos!




Topher wrote:

Hope you guys can help, whilst I try I have to admit to not being very good
at this...

I have a sheet with rows of data. The unique identifier (UI) is in column
B. By using a userform I would like to insert data in in the row belonging
to the UI.

On the useform, as the UI exisits I assume I need to use a combo box so the
user can select the UI. I then want them to enter a number of answers in
boxes to provide the data, and for this I can use text box's? So far so
good. What I seem to be missing is how do I get the data entered to go to
the correct row and cells?

Thanks in advance

--

Dave Peterson
.