Macro to hide columns and provde an active cell in column Bs next blank cell upon opening the workbook

[Raceman]

Hi,I'm having some problem figuring out why the following code is not working. When I open my file, I want a macro to automatically hide columns G -Z if they were unhidden when the file was last saved. And I want the active cell to be the next open cell (or blank cell) in Column B (starting with B4). My range in column B where data will go is B4:B1003

 Private Sub Workbook_Open()
'This Hides Columns G-Z when file is opened
'This code opens Excel to W_Log sheet and to the next record in column B
 
Worksheets("W_Log").Select
Sheets("W_Log").Columns("G:Z").Hidden = True
    Dim iRow As Long
    iRow = Cells(Rows.Count, "b").End(xlUp).Row - 5
    If iRow < 1 Then iRow = 1
    ActiveWindow.ScrollRow = iRow
    Cells(Rows.Count, "B").End(xlUp).Offset(1).Select

End Sub

What keeps happening is, it hides the Columns as expected, but the active cell goes to B1005. Why?
Help would definitely be appreciated

 

[ChrisM]

Your macro is fine, this sounds like a worksheet issue. The ".End" command will pick up empty cells that have spaces, or that Excel registers as in-use for various reasons. Use your vertical scrollbar and move down to the last row it takes you to, then delete rows from there UP to your last used row, then save the file. Then try the macro again, or, just use ctrl+up arrow to replicate the ".End" behavior. Put your cursor in cell B3000 or whatever and hit ctrl+up and see where it takes you.

 

[Raceman]

Ahhh thanks, I should have known! Great help thanks again. Can I add something to this? I wondered if I could include , upon opening the workbook, automatic protection is put in place so that a password is required to make changes to the "W_Log" sheet?

 

[ChrisM]

Sure, just set up the protection on the sheet as you normally would. Then in your workbook_open sub, just add this row:

Sheets("W_Log").Protect "yourpw"

only include the password bit if you use a password, otherwise just stop after .Protect

Obviously anyone who reads your VBA code will see your password, so if this is an issue you will need to lock your VBA code as well.

 

[Raceman]

Sorry, I'm not understanding where this line of code "Sheets("W_Log").Protect "yourpw""

should go in my code above on 1st posting. When I placed it at the end or the beginning it stopped the macro. Also, when you say, "set up the protection as you normally would" I was hoping this step (when the excel file opens) would automatically password protect the whole sheet, and possibly prompt window asking the user for a password (I know I neglected to say that before), and maybe if possible, a choice in that prompt window to select open as read only (not sure that part is easily done...if not I will not worry about). I would be very happy if someone opens the file and doesn't know the password, but could look view the information, but not be able to make changes.