0

So, I have the below code, that is hiding all rows with 1 in column. Each time when the file name has to be changed I have to change the code.

Workbooks("New R.M.S. Holiday Planner.xlsm").Worksheets("Planner").Activate

Instead of New R.M.S. Holiday Planner.xlsm I would like to point this to Cell A1, where I will have file name.

Probably it is something silly, but I can't work it out. Thanks for your help.

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Workbooks("New R.M.S. Holiday Planner.xlsm").Worksheets("Planner").Activate

With Sheets("Planner")
            Rows("11:1897").EntireRow.Hidden = False
            Application.ScreenUpdating = False
    For Each Cell In Range("BE10:BE1897")
    If Cell.Value = 1 Then Cell.EntireRow.RowHeight = 0.00001
Next Cell
Range("Y2:AF2").Select
    ActiveCell.FormulaR1C1 = "S&S Days"
    Range("A1").Select
 '   .Protect Password:="folder"
        Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End With
Else
If CheckBox1.Value = False Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Workbooks("New R.M.S. Holiday Planner.xlsm").Worksheets("Planner").Activate
With Sheets("Planner")
    For Each Cell In Range("BE10:BE1897")
    If Cell.Value = 1 Then Cell.EntireRow.RowHeight = 12.75
Next Cell
Range("Y2:AF2").Select
    ActiveCell.FormulaR1C1 = "All"
    Range("A1").Select
        Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End With
End If
End If
End Sub
SpoonTea
  • 17
  • 3
  • Is `New R.M.S. Holiday Planner.xlsm` the same workbook that you run the code? If so then you can simply use `ThisWorkbook` like this `ThisWorkbook.Worksheets("Planner").Activate`. Also, activate/select is a very bad practice and should be avoided unless it's absolutely necessary, read [this answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to learn how to avoid using select/activate. – Raymond Wu Sep 21 '21 at 02:34
  • For this code it is same workbook, however I have some other code too, which is fetching data from another file and I would like to be able to have dynamic workbook name. Thanks – SpoonTea Sep 21 '21 at 02:46
  • Is the cell `A1` in `New R.M.S Holiday Planner.xlsm` which is also `ThisWorkbook`? Assuming that the cell `A1` is in `Planner` worksheet then you can make a string variable and do something like `fileName = ThisWorkbook.Worksheet("Planner").Cells(1,1).Value` then you refer it as `Workbooks(fileName)` @SpoonTea – Raymond Wu Sep 21 '21 at 02:53
  • The other file name is not changing. Only New R.M.S. Holiday Planner.xlsm is changing. It will be New R.M.S. Holiday Planner 2021 - 2022.xlsm then New R.M.S. Holiday Planner 2022 - 2023.xlsm. Every time when I have to setup new file, I have to go through code and change name of the New R.M.S. Holiday Planner file. I've created this for another department and I would like them to be able to setup new one without my involvement. Unfortunately their knowledge of vba code is even lower then mine. Hopefully this helps. Thanks – SpoonTea Sep 21 '21 at 02:56
  • 1
    So if the workbook that's running the code is the one that's always changing name, you can simply replace `Workbooks("New R.M.S. Holiday Planner.xlsm")` to `ThisWorkbook` and that's all you need. `ThisWorkbook` always refer to the workbook that runs the code, no matter what the file name is. @SpoonTea – Raymond Wu Sep 21 '21 at 02:58
  • Thank you. I'm reading article about using activate/select right now. – SpoonTea Sep 21 '21 at 03:00

0 Answers0