VBA to do certain tasks on file opening

[jdhfch]

Hi there,

I am trying to create a VBA project that will run on opening a password protected workbook. I want it to populate the username in E1, then to use that username to force filtering Column C, based on a lookup in cell F2 (looking up their actual name based on E1). This is the code I am using, it all works in isolation, but when put together, the Auto filter isn't running. Can anyone help me, please?

Private Sub Workbook_Open()
ActiveSheet.Unprotect "Password"
Dim strUN As String

strUN = Environ("Username")

Worksheets(1).Range("E1").Value = Environ("Username")

Sheets("2025 Rebates").Range("A3").AutoFilter Field:=3, Criteria1:=Cells(2, 6).Value ' this line filters by a cell - A3 is the title line, 3 is the column to filter Cells = column and row number for the cell to filter'
ActiveSheet.Protect "Password"
End Sub

Many thanks

jdhfch

 

[jkpieterse]

In several places your code isn't clear which worksheet it operates on:
Worksheets(1), is that a different worksheet from Sheets("2025 Rebates") ? Why use Worksheets in the first and only Sheets in the second?
This piece:
Criteria1:=Cells(2, 6).Value

refers to cell F2 on the active worksheet, is that the correct worksheet to take cell F2 from?

Ans last but not least: by omitting the workbook information, all items refer to the active workbook, which isn't necessarily always the workbook that runs this code ("ThisWorkbook").

 

[jdhfch]

In several places your code isn't clear which worksheet it operates on:
Worksheets(1), is that a different worksheet from Sheets("2025 Rebates") ? Why use Worksheets in the first and only Sheets in the second?
This piece:
Criteria1:=Cells(2, 6).Value

refers to cell F2 on the active worksheet, is that the correct worksheet to take cell F2 from?

Ans last but not least: by omitting the workbook information, all items refer to the active workbook, which isn't necessarily always the workbook that runs this code ("ThisWorkbook").

Thank you for the response, Jan.

I am not very good at VBA, and I have tried to cobble to different projects together, hence the confusion. Everything is in the same workbook and on the same worksheet and the worksheet is called "2025 Rebates"

 

[jkpieterse]

Fair enough. How does this work:

Private Sub Workbook_Open()
    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Worksheets("2025 Rebates")
    sh.Unprotect "Password"
    sh.Range("E1").Value = Environ("Username")
    sh.Range("A3").AutoFilter Field:=3, Criteria1:=sh.Cells(2, 6).Value    ' this line filters by a cell - A3 is the title line, 3 is the column to filter Cells = column and row number for the cell to filter'
    sh.Protect "Password"
End Sub

 

[jdhfch]

Fair enough. How does this work:

Private Sub Workbook_Open()
    Dim sh As Worksheet
   
    Set sh = ThisWorkbook.Worksheets("2025 Rebates")
    sh.Unprotect "Password"
    sh.Range("E1").Value = Environ("Username")
    sh.Range("A3").AutoFilter Field:=3, Criteria1:=sh.Cells(2, 6).Value    ' this line filters by a cell - A3 is the title line, 3 is the column to filter Cells = column and row number for the cell to filter'
    sh.Protect "Password"
End Sub

Thank you. It is the same. It runs the Username part, but the filter isnt applied

 

[jkpieterse]

If you record a macro while manually applying the filter, what does it record?

 

[jdhfch]

If you record a macro while manually applying the filter, what does it record?

Sorry Jan, it was my fault... It works perfectly, thank you.