Protected cell problem

Topic

New Reply

Morning all

I have a worksheet that has a button that connects to Bing Maps and this works when the workbook is locked

I have then recorded a macro that opens the ‘External Data’ toolbar and refreshes some rates I have from X-Rates, this works fine but not if the workbook is locked, it shows the error below and if I debug it shows ActiveWorkbook.RefreshAll

any ideas please?

Reply | Quote

Replies

Update

I had a thought that it might be to do with the fact that the cells I wanted updated from the web were ‘locked’ so I editted them to be unlocked before locking down the worksheet but with the same result.

I then thought that if I used the ActiveSheet.Unprotect “password” and ActiveSheet.Protect “password” method it would work but now it says I can’t edit a locked worksheet.

This is how my Macro looks

Code:

Sub rates()
'
' rates Macro
' Macro recorded 12/09/2009 by Steve Hocking
'
' Keyboard Shortcut: Ctrl+h
'
 ActiveSheet.Unprotect "password"
	ActiveWorkbook.RefreshAll
 ActiveSheet.Protect "password"
End Sub

Reply | Quote

Do you have more than one worksheet in this workbook?

The message says that a worksheet is protected, so I guess this must be a different worksheet to the one you have just unprotected.

Reply | Quote

Do you have more than one worksheet in this workbook?

The message says that a worksheet is protected, so I guess this must be a different worksheet to the one you have just unprotected.

Well thought out Stuart that is what it is, I guess the I can use protect.activeworkbook instead, I will go and have a try

Thanks

Reply | Quote

Do you have more than one worksheet in this workbook?

The message says that a worksheet is protected, so I guess this must be a different worksheet to the one you have just unprotected.

Hi Stuart

No joy with that it bugs as it did in the first example that I posted,viz:

Morning all

I have a worksheet that has a button that connects to Bing Maps and this works when the workbook is locked

I have then recorded a macro that opens the ‘External Data’ toolbar and refreshes some rates I have from X-Rates, this works fine but not if the workbook is locked, it shows the error below and if I debug it shows ActiveWorkbook.RefreshAll

any ideas please?[/color]

Any other suggestions please?

Reply | Quote

Hi Stuart

No joy with that it bugs as it did in the first example that I posted,viz:

Morning all

I have a worksheet that has a button that connects to Bing Maps and this works when the workbook is locked

I have then recorded a macro that opens the ‘External Data’ toolbar and refreshes some rates I have from X-Rates, this works fine but not if the workbook is locked, it shows the error below and if I debug it shows ActiveWorkbook.RefreshAll

any ideas please?

Any other suggestions please?

Just for kicks, try the following:

Code:

Public Sub UProt()
   Dim oSht As Worksheet
	   For Each oSht In Worksheets
		   oSht.Unprotect
	   Next oSht
		   '
		   '
	   'Run your code here
		   '
		   '
	   For Each oSht In Worksheets
		   oSht.Protect
	   Next oSht
   End Sub

Reply | Quote

Just for kicks, try the following:

Code:

Public Sub UProt()
   Dim oSht As Worksheet
	   For Each oSht In Worksheets
		   oSht.Unprotect
	   Next oSht
		   '
		   '
	   'Run your code here
		   '
		   '
	   For Each oSht In Worksheets
		   oSht.Protect
	   Next oSht
   End Sub

Hi Don

Thanks for the input, when I use this should I put the passowrd in anywhere because what happened when I ran the above code it

a. opened a dialog box saying enter password but I do not want the users to know the password
b. ran the web query refresh and then gave me another dialog to enter the password for re-locking
c. In then gave me the message as below but it did seem to have refreshed everything from the web and relocked to worksheet

(editted 17:02 as Iforgot to upload the image)

Reply | Quote

Steve,

Try this :

[codebox]Public Sub UnProtectMe()
Dim oSht As Worksheet
For Each oSht In Worksheets
oSht.Unprotect “MyPassword”
Next oSht
‘
‘
‘Run your code here
‘
‘
For Each oSht In Worksheets
oSht.Protect “MyPassword”
Next oSht
End Sub[/codebox]

Reply | Quote

Steve,

Try this :

[codebox]Public Sub UnProtectMe()
Dim oSht As Worksheet
For Each oSht In Worksheets
oSht.Unprotect “MyPassword”
Next oSht
‘
‘
‘Run your code here
‘
‘
For Each oSht In Worksheets
oSht.Protect “MyPassword”
Next oSht
End Sub[/codebox]

Hi Jezza

Thank you but I still get the same error as above although I do not get the input boxes now asking for the password, just to be clear:

1. I have 2 sheets, 1 called Tariff and 1 called volume
2. Both WorkSheets are locked with the same password
3. The Workbook is not locked
4. The only code I am inserting into the middle of your and Don’s example is ActiveWorkbook.RefreshAll
5. I have changed the password in the “mypassword” fields to my password

Does this shed any further light on y problem?

Thanks

Reply | Quote

Morning all

I have found a workaround for the time being by moving the web query of the current page and then hiding the tabs, this was I can lock by tariff sheet so that the formulas stay intact. Not ideal but better than last night

Thanks for all the input

Reply | Quote

Good evening

Could somebody help and tell me how to apply this unprotect /protect to just 1 specific worksheet?

Reply | Quote

Good evening

Could somebody help and tell me how to apply this unprotect /protect to just 1 specific worksheet?

Assuming oSht is a pointer to the worksheet in question
[indent]oSht.Unprotect “MyPassword”
[/indent]

Reply | Quote

Hi Stuart

Thanks for the quick response.

The worksheet in question is parts and this is the sub Iam tring to assign is

Sub Unprotect()

Parts.Unprotect “Steve1910”
ActiveWorkbook.RefreshAll
Parts.Protect “Steve1910”

End Sub

Bit I just get the ‘debug dialog’ and when I debug it highlights the unprotect line?

Reply | Quote

Is Parts a pointer to the worksheet? Or is it the name of a worksheet?

If it is the name of the sheet then you can use
[indent]ActiveWorkbook.Worksheets(“Parts”).Unprotect “Steve1910”

[/indent]

Reply | Quote

Hi Stuart

Thanks for your effort and advice. There must be something else wrong that I need to investigate because I am still getting the message that the cell(s) I amtrying to update are protected but they are all on this sheet so I assume would be unprotected whilst the active,sheet was refreshed was refreshed and then protected afterwards. Just in case I stripped any other macros out of the entire workbook and made sure that the othere 3 worksheets were unprotected before and after?

Thanks

Reply | Quote