Excel 2010 - Userform - Vlookup a textbox and display in another textbox

[TheShyButterfly]

Thanking you in advance for your time.

I have a Userform in which the user enters in a four digit code number into "txt_SAC_No" texbox.

When the user moves to the next data entry field ("txt_INC_Recorded_By" textbox), I would like the value of the of the Vlookup to return the data into the "txt_Site_Address" textbox.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Sac#:
[/TD]
[TD="align: center"]Address:
[/TD]
[/TR]
[TR]
[TD="align: center"]7171[/TD]
[TD="align: center"]should pop up the results in this box[/TD]
[/TR]
</tbody>[/TABLE]

The reason why I need the 'Address" as a textbox is if the User enters the code '0' (which means we don't have an address) it should prompt the user to manually enter the address details in the textbox, maybe the user could be prompted with a message in the address field to say "please enter address details".

If the User enters 0, then I'd like it to show ..... (example)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Sac#:
[/TD]
[TD]Address:
[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD]Please enter address manually[/TD]
[/TR]
</tbody>[/TABLE]

I do not want the manually entered address details to be added to any lists - its a once only entry, which will be saved to the excel sheet.

I don't have code for trying to get the results from the Vlookup (couldn't find anything that would remotely work). Of course when I put it in the cell and do the vlookup, it works fine I don't know how to get it in the form (=Vlookup(txt_SAC_No,SiteAddress,2,0)


And just a quickie query please....
I currently have a auto increment number when the form opens, however I would like to have the number with a prefix Inc20150001 etc
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD="align: center"]Security Incident #[/TD]
[/TR]
[TR]
[TD="align: center"]Inc20150001[/TD]
[/TR]
</tbody>[/TABLE]


The following code is for the automatic increment numbering:

Code:

Private Sub UserForm_Initialize()
'This works - adds date and time when form is opened.
Me.txt_Date_Recorded = Date
Me.txt_Date_Recorded.Value = Format(Date, "dd/mm/yyyy")
Me.txt_Day_Recorded.Value = Format(Date, "ddd")
Me.txt_Time_Recorded.Value = Format(Time, "HH:mm")

'Now I am trying to get the Auto increment txtbox to _
work so that the prefix "Inc" goes before the auto number_
'at the moment it increments, but not like Inc20150001

Me.txtSEC_INC_No.Enabled = True
Dim irow As Long
Dim ws As ws_Incident_Details
Set ws = ws_Incident_Details
'find last data row from database'
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Row

If ws.[a2].Value = "" Then
Me.txtSEC_INC_No.Text = 9111 ' don't know what this is for
Else
Me.txtSEC_INC_No.Text = ws.Cells(irow, 1).Value + 1
End If


End Sub


If you need further clarification, please don't hesitate to ask

Thank you very much
The Shy Butterfly

 

[XLfriend777]

Hi ShyButterfly - For the portion of your question pertaining to including "Inc" in front of the number, have you considered using a custom format, something like "Inc"# that would put Inc in front of every number in all cells that are formatted that way? Hope this helps.

 

[TheShyButterfly]

Hi goesr, thank you for responding

Thank you for your suggestion I already have the Incident Number column on my data capture sheet formatted with the custom format with the prefix "Inc" and that is wonderful, but that doesn't display in the textbox in the Userform, which is where I would also like it displayed.

If someone out there has a wonderful vba code I could use to have the next incremented number already formatted in the "Inc######" in the actual textbox on the Userform, then I'd be very grateful, because then the value/data would automatically be transferred over to the data capture sheet in that format.

Any takers ?
Thank you again for taking the time to help me out

Cheers,
TheShyButterfly