Solved: UserForm ,Vlookup,Validation Combo To TextBox

[Rob342]

Hi Guys,

I am in the process of Creating a user form, 1st One ever!
I have got so far but need some help to guide me through a couple of bits i can't get my head around, have tried all routines to no avail.

1 User selects a panel code from a combo box, then in the text box next to it i would like the description of the panel inserted in the text box.

when i get the routine for this i can fill in the process no details with the same vba routine.

2 User fills in form details, then from the ModelId+PanelNo+ProcessNo to match (P_P_M in srodata) do a Vlookup to fill in the Rts Number & description,Mech Time,Paint Time & Panel code from the SroData sheet.

Its a tall order but if somebody can help or an example it would be appreciated.

Attached copy for ref

Rob342

[mikerickson]

Part 1), try code like this
Private Sub cboPanNo_Change()
'// If panel changes chnge the text desription TxtPanNo
With cboPanNo
If -1 < .ListIndex Then
TxtPanNo.Text = Range("PanelNo").Cells(.ListIndex + 1, 2).Value
End If
End With
End Sub

[Rob342]

Hello Mike

Thanks for the code much appreciated, works like magic, have now filled in 3 missing bits ie Model ID, Panel No, Process No txt Boxes.

Just the VBA Lookup up to get right now then hopefully i can move on to tidy the whole thing up with a bit of luck.

Regards
Rob

[shrivallabha]

This example may help you some way. You need to work out the flowsheet (activities) and then work on the code. Posts #4, #5 and #7
http://www.vbaexpress.com/forum/showthread.php?t=36057

[Rob342]

Hi shrivallabha, Mike

Thanks for info it might take some time to look at all that info.

I have added a string to get to the vlookup value but it errors out with various messages ?? any suggestions, also why does it not fill the txtboxRtsCode automatically, another routine not Change?



Private Sub TxtRtsCode_Change()
Dim RtsCode As String
Dim ws As Worksheet
Set ws = Worksheets("Srodata")
With TxtRtsCode
RtsCode = cboPanNo.Value & ModID.Text & cboProcNo.Value
TxtRtsCode.SetFocus
TxtRtsCode.Value = RtsCode
TxtRtsCode.Value = Application.WorksheetFunction.VLookup(TxtRtsCode.Value,
Worksheets("SroData").Range("B2:I25"), 2, False)
End With
End sub




Rob

[shrivallabha]

Hi Rob,

It is little difficult to guess at the moment. Can you explain a bit more as to what you are setting out to achieve?

Do you want to txtboxRtsCode to have an updated value based on the other textbox?

[Rob342]

Hi Shirvallabha

I have got the Vlookup working now, forgot to convert the strings back to a number with the code below, so thats another step done.

Private Sub TxtRtsCode_Change()
Dim RtsCode As Integer
TxtRtsCode.SetFocus
RtsCode = cboPanNo.Value & ModID.Text & cboProcNo.Value
TxtRtsCode.Text = Application.WorksheetFunction.VLookup(Val(RtsCode), Worksheets("SroData").Range("B2:I25"), 4, False)
End Sub


The event does not fire unless it goes into "Change"

To simplfy it, when the model, PanelNo and Process No have been filled in then the TxtRtsCode box gets updated automatically, when this txtbox has a value then it will fire the event to complete the 3 Txtboxes (Mech Time, Rts Time and Panel Code).

Do you think a routine combining all 3boxes <> blank then update the TxtRtsCode box ?
but then a what if situ, if somebody changes 1 of the fields to another value.

Rob

[shrivallabha]

Sorry for late reply. One way would be forcing / coercing the user to perform action only in one direction. In the userform intialize event:

Private Sub UserForm_Initialize()
Me.ModID.Enabled = False
Me.cboProcNo.Enabled = False
Me.TxtRtsCode.Enabled = False


With this the user will be forced to enter some data in cboPanNo. Then for cboPanNo will have its own event code as:
Sub cboPanNo_Change()
Msgbox "Now, please enter MOD ID value!"
Me.ModID.Enabled = True
Me.ModID.Setfocus
End Sub
Then use similar code for ModID.

But then change the code for cboProcNo like (Note change in event from Change to Exit)
Sub cboPanNo_Exit()
Me.TxtRtsCode.Enabled = True
'Your code here
Dim RtsCode As Integer
TxtRtsCode.SetFocus
RtsCode = cboPanNo.Value & ModID.Text & cboProcNo.Value
TxtRtsCode.Text = Application.WorksheetFunction.VLookup(Val(RtsCode), _
Worksheets("SroData").Range("B2:I25"), 4, False)
End Sub
So as soon as you exit cboPanNo it will get you updated TxtRtsCode.

[Rob342]

Hi Shivarallabha

Thanks for pointing me in the right direction tried the code works a treat, i should be able to finish the project now hopefully.

If i get stuck again i'll create another post.

Thanks for you help & time much appreciated.

Regards
Rob