shaeto
07-11-2016, 11:26 AM
Dear All,
I'm quite new to VBA and I'm still trying to find my way out. Your help will be much appreciated.
Here are my issues.
I have a userform which search for information in a worksheet (sheet1).
Once populated, I would like the user to check an option button (Yes or No) to confirm that the data from the search was verified (checked) against a hardcopy.
I managed to have the search worked but I can't figure out how to put a 'Y' or a 'N' in a specific cell with respect to the searched data.
In a nutshell this what I'm trying to do:
I have my database in sheet1 as follows
16596
When user click on the ‘Userform’ button the following userform is displayed
16597
User enter the ‘ID’ in the textbox and click on the ‘Search’ button
16598
User then match details against a hardcopy and click on the ‘Yes’ option button in the last frame ‘Checked?’.
When the ‘Save’ button is clicked, the corresponding cell in sheet1 under column 'Checked?' is marked with a ‘Y’ or else an 'N' for no.
16599
My codes are as followed:
Private Sub CommandButton1_Click()
'When the 'Search' button is clicked the following are activated
'1.Specifying what to do in case of error, i.e. go to MyErrorHandler describes down
On Error GoTo MyErrorHandler:
'2.Declaration of variables
Dim ID As Integer
Dim Lookup_Range As Range
Dim Det_1 As String
Dim Det_2 As String
'3.Specifying the results
ID = TextBox1.Value
Set Lookup_Range = Worksheets("Sheet1").Range("A1:E1048576")
'4.Applying the VLookUp Functions
Det_1 = Application.WorksheetFunction.VLookup(ID, Lookup_Range, 2, False)
Label3.Caption = Det_1
Det_2 = Application.WorksheetFunction.VLookup(ID, Lookup_Range, 3, False)
Label5.Caption = Det_2
Exit Sub
'5.Handling errors
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "ID not found."
End If
End Sub
Attached is a copy of my workbook.
Thank you for your valuable help.
Shameem
I'm quite new to VBA and I'm still trying to find my way out. Your help will be much appreciated.
Here are my issues.
I have a userform which search for information in a worksheet (sheet1).
Once populated, I would like the user to check an option button (Yes or No) to confirm that the data from the search was verified (checked) against a hardcopy.
I managed to have the search worked but I can't figure out how to put a 'Y' or a 'N' in a specific cell with respect to the searched data.
In a nutshell this what I'm trying to do:
I have my database in sheet1 as follows
16596
When user click on the ‘Userform’ button the following userform is displayed
16597
User enter the ‘ID’ in the textbox and click on the ‘Search’ button
16598
User then match details against a hardcopy and click on the ‘Yes’ option button in the last frame ‘Checked?’.
When the ‘Save’ button is clicked, the corresponding cell in sheet1 under column 'Checked?' is marked with a ‘Y’ or else an 'N' for no.
16599
My codes are as followed:
Private Sub CommandButton1_Click()
'When the 'Search' button is clicked the following are activated
'1.Specifying what to do in case of error, i.e. go to MyErrorHandler describes down
On Error GoTo MyErrorHandler:
'2.Declaration of variables
Dim ID As Integer
Dim Lookup_Range As Range
Dim Det_1 As String
Dim Det_2 As String
'3.Specifying the results
ID = TextBox1.Value
Set Lookup_Range = Worksheets("Sheet1").Range("A1:E1048576")
'4.Applying the VLookUp Functions
Det_1 = Application.WorksheetFunction.VLookup(ID, Lookup_Range, 2, False)
Label3.Caption = Det_1
Det_2 = Application.WorksheetFunction.VLookup(ID, Lookup_Range, 3, False)
Label5.Caption = Det_2
Exit Sub
'5.Handling errors
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "ID not found."
End If
End Sub
Attached is a copy of my workbook.
Thank you for your valuable help.
Shameem