Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill userform textbox from userform listbox clik event | Excel Programming | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
Userform to enter values and shown in same userform in list | Excel Programming | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming |