+ Reply to Thread
Results 1 to 2 of 2

Protect a Worksheet (Disable Macro Button)

Hybrid View

PRodgers Protect a Worksheet (Disable... 06-11-2009, 09:45 AM
royUK Re: Protect a Worksheet... 06-11-2009, 10:21 AM
  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    N.Ireland
    MS-Off Ver
    Excel 2003
    Posts
    76

    Protect a Worksheet (Disable Macro Button)

    I have a spreadsheet that includes command buttons used to run macros for calculations. I have a macro that is used to lock the worksheet and protect it, i want the command buttons with the macros for the calculations to be be disabled when the lock worksheet macro is run. Can anyone help me with this:

    My code is:

    
    Public Sub unlocksheet()
    'unlock the worksheet
    
    On Error Resume Next
    ActiveSheet.Unprotect Password:="HCAI"
    
    End Sub
    Public Sub locksheet()
    'lock the worksheet
    
    On Error Resume Next
    ActiveSheet.Protect Password:="HCAI"
    
    End Sub
    
    
    Sub calculateYearlyQuarter()
    
    ' Lr = "Last Row"
    
        Dim Lr As Long
         
        Lr = Range("I" & Rows.Count).End(xlUp).Row
        
    'Refers to Cells 1 = "1" and 5 = Column "E"
         
        With Cells(2, 39).Resize(Lr - 1, 1)
            .Formula = "=""Q""&INT(1+MOD(MONTH(I2)-1,12)/3)"
            .Font.ColorIndex = 3
            .Font.Bold = True
        End With
    
    End Sub
    
    Sub calculateFinancialQuarter()
    
    ' Lr = "Last Row"
    
        Dim Lr As Long
         
        Lr = Range("I" & Rows.Count).End(xlUp).Row
    
    'Refers to Cells 1 = "1" and 6 = Column "F"
        
        With Cells(2, 40).Resize(Lr - 1, 1)
            .Formula = "=""Q""&INT(1+MOD(MONTH(I2)-4,12)/3)"
            .Font.ColorIndex = 3
            .Font.Bold = True
        End With
        
    End Sub

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Protect a Worksheet (Disable Macro Button)

    Set the commandbuttons enabled property to False
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1