PDA

View Full Version : Excel 2010 – VBA Userform - Vlookup Textbox1 and return value to textbox2



ShyButterfly
03-13-2015, 11:53 PM
Hi there, thank you for taking the time to look at this.

I have a userform in which the user enters in a 4 digit number into a textbox (txtSacNum), and in another textbox (txtSiteAddress) on the form I would like it to return the value of the vlookup value of the 4 digit code, which is derived from a named range (SiteAddress) on another sheet(Alarm Response List).
There are instances where there is no 4 digit code and the user will need to manually enter the address in the txtSiteAddress textbox. I don’t want the manually entered address to be added to the Alarm Response List because it is a once off entry.

I have tried to modify some codes I found, but they are always referring to an Input Box and showing the result in a dialog box – I don’t want that. No matter how I have tried to adapt the code to return the value to of the txt.SiteAddress textbox on the Userform it doesn’t work (I have fought with this for days).

Objects:
Userform Name: SW_Security_Incident_Tracker
Textboxes: txt_SAC_No and txt_Site_Address
Data retention sheet (form data is saved to on the ‘save’ command button):
Sheet Name: Incident Details
Sheet Code Name: ws_Incident_Details (this is what I would prefer what the sheet would be coded for)
Vlookup data sheet:
Sheet name: Alarm Response List
Sheet Code name: ws_AlarmResponseList (prefer to reference this name)
***Data range: ='Alarm Response List'!$C:$D
Named Range: SiteAddress (C column has SAC number, D column has Address of SAC number)

*** The named range (SiteAddress) is columns C:D (to allow for any new addresses in the future to be included in the named range)


I would be immensely grateful for any help … if you would like me to provide you with what coding I have tried to adapt, please let me know (I just thought that starting from a fresh slate would be less confusing).

I’ve tried to attached a sample file but it just won't accept it ... I'll keep trying :)

Most gratefully
Shy Butterfly