none
help in VBA Code RRS feed

  • Question

  • Dears,

    i have a VBA code for userform that let me retrieve some data from a schedule table based on some criteria,

    the schedule source need to view some columns and hide the others, i can retrieve the schedule with the hidden columns by hiding them in the source, but i didn't need that, i ask if i can hide them  in the retrieved data only

    the shown columns are: ("A, F, J, W, Y, Z, AA, AB, AC, AD, AG, AH")

    Private Sub CommandButton1_Click()
    Dim username As String, id As String, lid As String, mnth As String
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    With Me
    username = .TextBox1.Value
    id = .TextBox2.Value
    lid = .TextBox3.Value
    mnth = .ComboBox1.Value
    
    If Not username <> "" Or _
        IsError(Application.Match(username, Range("Table_Source[Username]"), 0)) Then
            MsgBox "Pls input a valid username.", vbInformation
            .TextBox1.SetFocus
        GoTo e
    End If
    
    If Not id <> "" Or _
        IsError(Application.Match(id, Range("Table_Source[ID]"), 0)) Then
            MsgBox "Pls input a valid ID.", vbInformation
            .TextBox2.SetFocus
        GoTo e
    End If
    
    If Not lid <> "" Or _
        IsError(Application.Match(Val(lid), Range("Table_Source[Login]"), 0)) Then
            MsgBox "Pls input a valid LoginID.", vbInformation
            .TextBox3.SetFocus
        GoTo e
    End If
    
    If Not mnth <> "" Or _
        IsError(Application.Match(mnth, Range("Table_Source[Month]"), 0)) Then
            MsgBox "Pls use dropdown to select a month.", vbInformation
            .ComboBox1.SetFocus
        GoTo e
    End If
    
    End With
    
    'using countif is more easy then match as there is no error trap to be used
    
    With Sheet4
        .Visible = xlSheetVisible
        .Cells.Clear
    End With
    
    With Range("Table_Source")
        .AutoFilter 33, username
        .AutoFilter 6, id
        .AutoFilter 34, lid
        .AutoFilter 2, mnth
    End With
    Range("Table_Source[#All]").SpecialCells(xlCellTypeVisible).Copy Sheet4.[a1]
    Range("Table_Source[#All]").AutoFilter
    Unload Me
    Sheet4.Activate
    
    If (Application.CountIf(Range("Table3[Username]"), username) + _
        Application.CountIf(Range("Table3[ID]"), id) + _
        Application.CountIf(Range("Table3[Login]"), lid)) > 0 Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Interface" Then ws.Visible = -1
        Next
    End If
    
    e:
    Application.ScreenUpdating = True
    End Sub
    
    Private Sub UserForm_Initialize()
    Dim r As Range
    
    With CreateObject("scripting.dictionary")
        For Each r In Range("Table_Source[Month]")
            If Not .exists(r.Value) Then .Add r.Value, 1
        Next
    If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
    End With
    
    End Sub
       
    thanks a lot, 

    Sunday, November 2, 2014 10:56 AM

Answers

  • Hi Ahmed,

    From the descrption, you didn't want to hide the data in source data but only in retrieving data. And based on the code you are using auto filter to filter the data and copy them into sheet4.

    A easy way to achieve the solution is that we can copy the all of data a temporary worksheet and remove the unwanted data and copy them into the taget worksheet.

    Another way is that use ADO to retrieve data and we can custom the SQL to filter data. Here is a sample to use ADO to filter the data we wanted for your reference:

    Sub FilterData()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;HDR=YES"""
    
    With cn
    
    .Open
    Set rst = cn.Execute("Select [Name],sales from [Sheet1$] where [name]='Jack5'")
    Sheet2.Range("A1").CopyFromRecordset rst
    End With
    
    End Sub

    You can get more detail about ADO from linke below:
    How To Use ADO with Excel Data from Visual Basic or VBA

    Microsoft ActiveX Data Objects Reference

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 4, 2014 2:31 AM
    Moderator

All replies

  • Dears,

    the mentioned columns: ("A, F, J, W, Y, Z, AA, AB, AC, AD, AG, AH") 

    in the table that called ("Table_Source") have a header that titled  

    Date Of Action Month ID Adherence ToTal Deduction Attendance# Tardiness (Break)# Using Mobil# Attitude# Comment# Username Login

    that maybe an additional info in developing the above code

    Sunday, November 2, 2014 8:21 PM
  • Hi Ahmed,

    From the descrption, you didn't want to hide the data in source data but only in retrieving data. And based on the code you are using auto filter to filter the data and copy them into sheet4.

    A easy way to achieve the solution is that we can copy the all of data a temporary worksheet and remove the unwanted data and copy them into the taget worksheet.

    Another way is that use ADO to retrieve data and we can custom the SQL to filter data. Here is a sample to use ADO to filter the data we wanted for your reference:

    Sub FilterData()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;HDR=YES"""
    
    With cn
    
    .Open
    Set rst = cn.Execute("Select [Name],sales from [Sheet1$] where [name]='Jack5'")
    Sheet2.Range("A1").CopyFromRecordset rst
    End With
    
    End Sub

    You can get more detail about ADO from linke below:
    How To Use ADO with Excel Data from Visual Basic or VBA

    Microsoft ActiveX Data Objects Reference

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 4, 2014 2:31 AM
    Moderator