Refreshing data on a protected worksheet

Anonymous
2012-12-12T13:32:38+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2012-12-12T18:39:00+00:00

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

Was this answer helpful?

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-12-19T09:05:41+00:00

    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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-12-12T22:10:48+00:00

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

    Trevor

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2012-12-12T15:59:10+00:00

    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

    Was this answer helpful?

    0 comments