Refreshing data on a protected worksheet

Question

I have a workbook which is linked to several data sources.

I want to be ableto protect one or more sheets so that all end users view the sheet the same (e.g stop colums widening etc).

But when I do it fails to be able refresh data on that sheet?

ANy ways round this I dond't see it on the list of options when you do prtect with password.

Trevor

Answer 1

Hi, try this

> Option Explicit

> Sub Hide_Rows()

> ' Hide_Rows Macro

> ' January 11, 2005

> ' From Frank Kabel

> ' Keyboard Shortcut: NONE

>

> Worksheet.Unprotect Password:="password"

>

> Dim RowNdx As Long

> Dim LastRow As Long

> Application.ScreenUpdating = False

> LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

> For RowNdx = LastRow To 1 Step -1

> If Cells(RowNdx, "B").Value = "x" Then

> Rows(RowNdx).Hidden = True

> End If

> Next RowNdx

> Application.ScreenUpdating = True

> Option Explicit

> Sub Hide_Rows()

> ' Hide_Rows Macro

> ' January 11, 2005

> ' From Frank Kabel

> ' Keyboard Shortcut: NONE

>

> Worksheet.Unprotect Password:="password"

>thisworkbook.RefreshAll

> Dim RowNdx As Long

> Dim LastRow As Long

> Application.ScreenUpdating = False

> LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

> For RowNdx = LastRow To 1 Step -1

> If Cells(RowNdx, "B").Value = "x" Then

> Rows(RowNdx).Hidden = True

> End If

> Next RowNdx

> Application.ScreenUpdating = True

> Worksheet.Protect Password:="password"

>

> End Sub

>

> Worksheet.Protect Password:="password"

>

> End Sub

Answer 2

Many thanks for your replies.  They do work with regards to updating the data.  But re-present me with the original problem the sheets where protected for .  And that is maintaining column widths so i can fit the data on to one screen width.  Once the worksheet is un-protected, data refreshed column widths expand to accommodate data length even though the column width is fixed and formatting set to wrap text.

Will i have to include a routine to reset fixed widths and formatting?

Trevor

Answer 3

Thanks for all the replies I'll give them a go tomorrow (10pm here!) and report back

Trevor

Answer 4

OK thanks for the pointer, I've found a macro that will unprotected and reprotect a worksheet, thanks  John F. Scholten

> Option Explicit

> Sub Hide_Rows()

> ' Hide_Rows Macro

> ' January 11, 2005

> ' From Frank Kabel

> ' Keyboard Shortcut: NONE

>

> Worksheet.Unprotect Password:="password"

>

> Dim RowNdx As Long

> Dim LastRow As Long

> Application.ScreenUpdating = False

> LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

> For RowNdx = LastRow To 1 Step -1

> If Cells(RowNdx, "B").Value = "x" Then

> Rows(RowNdx).Hidden = True

> End If

> Next RowNdx

> Application.ScreenUpdating = True

>

> Worksheet.Protect Password:="password"

>

> End Sub

> --

> John F. Scholten

I'm pretty poor at any type of scripting/prog, so where within this shoule I put my refresh data procedure, straight after worksheet.unprotect?

Thanks trevor

Answer 5

Hi, you can add a buttom to the spreadsheet where you link a macro which will unprotect your sheet first then refresh it and protect it again