Get data from hide worksheet in userform text boxes with vba using Vlookup

[ganesh_6663]

Hi Team,
Need to get data from hide worksheet in userform text boxes with vba using Vlookup application from DATABASE worksheet which is hiden while Useform is open..
When their is multiple excel file open to userform not getting data from DATABASE worksheet , tool giving error data not found .
After disable Application.Visible , tool working properly .
Help me to get data from hiden worksheet DATABASE while userform is active .
TOOL DOWNLOAD LINK.............


Code use to show and hide worksheet/userform , After disable this tool working properly.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Windows("Upload.xlsm").Visible = True
UserForm1.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Visible = True
Windows("Upload.xlsm").Visible = True
UserForm1.Hide
End Sub

Code user to get data in textbox using vlookup after clicking button

Private Sub CommandButton5_Click()
'Error for get data button******************
 If Reg1.Value = "" Then
 MsgBox ("Please Enter SERVER ID & Then click on Getdata")
 'Me.Reg1.Value = ""
 Exit Sub
 End If
 'On Error GoTo myerror
 On Error Resume Next
  If WorksheetFunction.CountIf(Sheets("DATABASE").Range("A:A"), Me.Reg1.Value) = 0 Then
 MsgBox ("Given SERVER ID not present in Macro Database")
 'Me.Reg1.Value = ""
 Exit Sub
 End If

    Reg2.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:F199191"), 6, 0)
    Reg3.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:E199191"), 5, 0)
    Reg4.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:H199191"), 8, 0)
    Reg5.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:N199191"), 14, 0)
    Reg6.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:K199191"), 11, 0)
    TextBox6.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:M199191"), 13, 0)
    TextBox4.Value = Application.VLookup(Reg1.Value, Sheets("DATABASE").Range("A2:Q199191"), 17, 0)
    UserForm1.Reg1.SetFocus

End Sub

Getting following error while worksheet is hiden and try to click on "Click here 1st Gedata From SER ID"

 

[dmt32]

Hi,
try using the Range.Find method to return the data to your userform

See if this update helps you

Private Sub CommandButton5_Click()
    Dim FoundCell   As Range
    Dim ServerID    As String
    Dim wsDatabase  As Worksheet
   
    Set wsDatabase = ThisWorkbook.Worksheets("DATABASE")
   
    ServerID = Me.Reg1.Value
   
    If Len(ServerID) = 0 Then
   
        MsgBox "Please Enter SERVER ID & Then click On Getdata", 48, "Server ID Required"
       
    Else
        Set FoundCell = wsDatabase.Columns(1).Find(ServerID, LookIn:=xlValues, lookat:=xlWhole)
        If Not FoundCell Is Nothing Then
           
            With FoundCell
                Me.Reg2.Value = .Offset(, 5).Value
                Me.Reg3.Value = .Offset(, 4).Value
                Me.Reg4.Value = .Offset(, 7).Value
                Me.Reg5.Value = .Offset(, 11).Value
                Me.Reg6.Value = .Offset(, 10).Value
                Me.TextBox6.Value = .Offset(, 12).Value
                Me.TextBox4.Value = .Offset(, 16).Value
            End With
           
        Else
           
            MsgBox ServerID & Chr(10) & "SERVER ID Not present in Database", 48, "Server ID Not Found"
           
        End If
    End If
   
    Me.Reg1.SetFocus
   
End Sub

solution not fully tested & may need to be adjusted to meet your specific project need

Dave

 

[ganesh_6663]

Really Thanks for help , but getting error "Method or data member not found "
Any changes i need to do?

 

[dmt32]

Really Thanks for help , but getting error "Method or data member not found "
Any changes i need to do?

that suggests some of the controls you listed do not exist in your userform

Check each control shown in BOLD do actually exist & if not, add them.

Me.Reg2.Value = .Offset(, 5).Value
                Me.Reg3.Value = .Offset(, 4).Value
                Me.Reg4.Value = .Offset(, 7).Value
                Me.Reg5.Value = .Offset(, 11).Value
                Me.Reg6.Value = .Offset(, 10).Value
                Me.TextBox6.Value = .Offset(, 12).Value
                Me.TextBox4.Value = .Offset(, 16).Value

Dave

 

[ganesh_6663]

Thanks Its working !

 

[dmt32]

welcome - appreciate feedback

Dave

 

[ganesh_6663]

Hi , really than for your help , need one more help .
By using same code how to get data from ".xlsx*"save in same folder.

 

[dmt32]

Hi , really than for your help , need one more help .
By using same code how to get data from ".xlsx*"save in same folder.

If you are saying that the Database worksheet is Stored in a separate workbook on same folder then perhaps this update to the code might do what you want

Private Sub CommandButton5_Click()
    Dim FoundCell   As Range
    Dim ServerID    As String, strFileName As String
    Dim wsDatabase  As Worksheet
    Dim wbDatabase  As Workbook
    
    On Error GoTo myerror
    strFileName = ThisWorkbook.Path & "\Database.xlsx"
    
    Application.ScreenUpdating = False
    
    Set wbDatabase = Workbooks.Open(strFileName, False, True)
    Set wsDatabase = wbDatabase.Worksheets("DATABASE")
    
    
    ServerID = Me.Reg1.Value
    
    If Len(ServerID) = 0 Then
    
        MsgBox "Please Enter SERVER ID & Then click On Getdata", 48, "Server ID Required"
        
    Else
        Set FoundCell = wsDatabase.Columns(1).Find(ServerID, LookIn:=xlValues, lookat:=xlWhole)
        
        If Not FoundCell Is Nothing Then
            
            With FoundCell
                Me.Reg2.Value = .Offset(, 5).Value
                Me.Reg3.Value = .Offset(, 4).Value
                Me.Reg4.Value = .Offset(, 7).Value
                Me.Reg5.Value = .Offset(, 11).Value
                Me.Reg6.Value = .Offset(, 10).Value
                Me.TextBox6.Value = .Offset(, 12).Value
                Me.TextBox4.Value = .Offset(, 16).Value
            End With
            
        Else
            
            Err.Raise 744, , ServerID & Chr(10) & "SERVER ID Not present in Database"
            
        End If
    End If
    
    
myerror:
    Me.Reg1.SetFocus
    If Not wbDatabase Is Nothing Then wbDatabase.Close False
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

Change FileName shown in BOLD as required

Dave

 

[ganesh_6663]

Thanks , Its working

 

[ganesh_6663]

One more question , i need to make "Application.Visible = True" QueryClose sub , but on run time error(While macro running and closed userform) userfom get closed and my Excel file remain hidden.
any solution for this , i try with "On Error Resume Next" but it will not give error but Excel file remain hide .
Any suggestion how to make Excel file make visible or closed hidden Excel file .

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'On Error Resume Next
Application.Visible = True
Windows("new - Copy.xlsm").Visible = True
UserForm1.Hide

On call UserForm Excel get hide , only UserForm show..

Sub CallUserForm()
UserForm1.Show (vbModeless)
 If Workbooks.Count > 1 Then
       Windows("new - Copy.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
    UserForm1.Show (vbModeless)
End Sub