Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    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.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='793073' date='12-Sep-2009 07:54']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.[/quote]


    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='793073' date='12-Sep-2009 07:54']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.[/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?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='stevehocking' post='793090' date='12-Sep-2009 10:44']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?[/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
    Regards
    Don

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='793093' date='12-Sep-2009 16:15']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
    [/quote]

    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)
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    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]
    Jerry

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='jezza' post='793096' date='12-Sep-2009 17:25']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][/quote]

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good evening

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

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='stevehocking' post='793435' date='15-Sep-2009 21:22']Good evening

    Could somebody help and tell me how to apply this unprotect /protect to just 1 specific worksheet?[/quote]
    Assuming oSht is a pointer to the worksheet in question
    oSht.Unprotect "MyPassword"

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  14. #14
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    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
    ActiveWorkbook.Worksheets("Parts").Unprotect "Steve1910"


  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •