Help with Workbook_Open

[Ruan]

Hello

Below is the code I run on Sheet 2 with a Worksheet_Change event. I would
like to run this code when the User opens the Workbook using the
Workbook_Open event. I am new to VBA, so any help will be greatly
appreciated.

Code from Sheet2 Module

Private Sub Worksheet_Change(ByVal Target As Range)

' Unprotecting Worksheet
Worksheets("Sheet 2").Unprotect Password:="1234"

' Unhide Rows before extracting Data
With Rows("9:409")
.EntireRow.Hidden = False
End With

' Target of Combo Box for Doctor List (F2)
If Target.Row = 2 And Target.Column = 6 Then

' Calculate criteria cell in case calculation mode is manual
Worksheets("Sheet 1").Range("AA2").Calculate
Worksheets("Sheet 1").Range("Data_Log") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("AA1:AA2"), _
CopyToRange:=Range("A8:H8"), Unique:=False

' Hide Blank Rows
Worksheets("Sheet 2").Unprotect Password:="1234"
With Range("B9:B409")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.EntireRow.Font.ColorIndex = 1
End With
End If

' Protecting Worksheet
Worksheets("Sheet 2").Protect Password:="1234", Scenarios:=True

End Sub


Thanks in advance
Ruan

 

[Melanie Breden]

Hi Ruan,

Below is the code I run on Sheet 2 with a Worksheet_Change event. I would
like to run this code when the User opens the Workbook using the
Workbook_Open event. I am new to VBA, so any help will be greatly
appreciated.

I assigned the table sheets to the ranges.
Save thet following macro in a normally module:

Sub Ruan()
With Worksheets("Sheet 2")
.Unprotect Password:="1234"
.Rows("9:409").Hidden = False

With Worksheets("Sheet 1")
.Range("AA2").Calculate
.Range("Data_Log") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("AA1:AA2"), _
CopyToRange:=Worksheets("Sheet 2").Range("A8:H8"), Unique:=False
End With

With .Range("B9:B409")
' of case no empty cells available
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.EntireRow.Font.ColorIndex = 1
End With

.Protect Password:="1234", Scenarios:=True
End With
End Sub

In the codemodule ThisWorkbook:

Private Sub Workbook_Open()
Ruan
End Sub

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Ruan]

Hello Melanie,

I entered your code as you showed, but I am receiving the following error -

"Compile error in hidden module: This workbook"

Here is my code that I entered in the Sheet 2 module
Sub DataFilter()
With Worksheets("Sheet 2")
.Unprotect Password:="1234"
.Rows("9:409").Hidden = False

With Worksheets("Sheet 1")
.Range("BD2").Calculate
.Range("Data_Log") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("BD1:BE2"), _
CopyToRange:=Worksheets("Sheet 2").Range("A8:AA8"),
Unique:=False
End With

With .Range("B9:B409")
' of case no empty cells available
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.EntireRow.Font.ColorIndex = 1
End With

.Protect Password:="1234", Scenarios:=True
End With
End Sub

Thanks for your time
Ruan

 

[Melanie Breden]

Hi Ruan,

I entered your code as you showed, but I am receiving the following error -

"Compile error in hidden module: This workbook"

Here is my code that I entered in the Sheet 2 module
Sub DataFilter()

you changed the procedure names of Ruan in DataFilter.
Did you also adapt in the Workbook_Open-Event?

Private Sub Workbook_Open()
DataFilter
End Sub


--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Melanie Breden]

Hi Ruan,

Hello Melanie,

I entered your code as you showed, but I am receiving the following error -

"Compile error in hidden module: This workbook"

Here is my code that I entered in the Sheet 2 module
Sub DataFilter()

the procedure does not belong into the codemodule of the table.
Provide in the VBE over Insert |Module a new module there and insert the procedure.
Is it now correct?

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Bob Phillips]

Ruan,

The new module, DataFilter, should be in a standard code module, not in the
ThisWorkbook module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ruan]

Hello Melanie,

I figure it out. I had the code in the Worksheet code module instead of the
General Code Module. Thanks so much for your help.

I need to add the following code, but seem to be getting an error -
Range("BA409:BD410").Copy Destination:=Range("A409")

It worked when I had it in this format -
Worksheets("Sheet 1").Range("BD2").Calculate
Worksheets("Sheet 1").Range("Data_Log") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Patient Data").Range("BD1:BE2"), _
CopyToRange:=Range("A8:AA8"), Unique:=False
Range("BA409:BD410").Copy Destination:=Range("A409")

I would greatly appreciate, if you could help me with this.
Thanks
Ruan

 

[Melanie Breden]

Hi Ruan,

I need to add the following code, but seem to be getting an error -
Range("BA409:BD410").Copy Destination:=Range("A409")

this command line alone does not produce an error message with me,
if the sheet is not protected.
How does the error message read exactly?

It worked when I had it in this format -
Worksheets("Sheet 1").Range("BD2").Calculate
Worksheets("Sheet 1").Range("Data_Log") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Patient Data").Range("BD1:BE2"), _
CopyToRange:=Range("A8:AA8"), Unique:=False
Range("BA409:BD410").Copy Destination:=Range("A409")

It is better, if you call the Worksheet before each Range.
Before the points of the Range data the object of the With instruction is set automatically.

With Worksheets("Sheet 1")
.Range("BD2").Calculate
.Range("Data_Log").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Patient Data").Range("BD1:BE2"), _
CopyToRange:=.Range("A8"), Unique:=False
.Range("BA409:BD410").Copy Destination:=.Range("A409")
End With


--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)