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]

Thanks , Its working

I overlooked something when updating the code - suggest use following version

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"
    
    
    ServerID = Me.Reg1.Value
    
    If Len(ServerID) > 0 Then
        
        Application.ScreenUpdating = False
        
        Set wbDatabase = Workbooks.Open(strFileName, False, True)
        Set wsDatabase = wbDatabase.Worksheets("DATABASE")
        
        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
        
    Else
        
        MsgBox "Please Enter SERVER ID & Then click On Getdata", 48, "Server ID Required"
        
    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

I don't have an immediate answer to your further question - may be better to start a new thread where likely to get more responses.

Dave

 

[ganesh_6663]

Yes, no issue Thanks