You are here:

Excel/macro within a formula

Advertisement


Question
Hi Tom

Hope all is good!

I have been trying to do the following and running into a road block...

i have the following code on the worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

 If Range("F3").Value = 2 Then
         
   On Error Resume Next
   ActiveSheet.unprotect Password:=Sheet1.Range("M1"), AllowFormattingCells:=True

       Columns("C:E").EntireColumn.Hidden = True
       
       Columns("F").EntireColumn.Hidden = False

       Sheets("Payment Comparison").ChartObjects("Payment Comparison").Visible = False
       
       Sheets("Payment Comparison").ChartObjects("Payment Comparison2").Visible = True
   
       Else

       Columns("C:E").EntireColumn.Hidden = False
       
       Columns("F").EntireColumn.Hidden = True

       Sheets("Payment Comparison").ChartObjects("Payment Comparison").Visible = True
       
       Sheets("Payment Comparison").ChartObjects("Payment Comparison2").Visible = False
   
       End If
         
   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("M1"), AllowFormattingCells:=True
    
End Sub

F3 is going to be either 1 or 2 based on a value from another worksheet, for example, if i pick a certain client, the result on f3 is either going to be 2 or 1, when i go to this worksheet(payment chart) which is where this code is on nothing happens, i will put whats on the entire worksheet so you have can a better idea....


Private Sub Worksheet_Change(ByVal Target As Range)

 If Range("F3").Value = 2 Then
         
   On Error Resume Next
   ActiveSheet.unprotect Password:=Sheet1.Range("M1"), AllowFormattingCells:=True

       Columns("C:E").EntireColumn.Hidden = True
       
       Columns("F").EntireColumn.Hidden = False

       Sheets("Payment Comparison").ChartObjects("Payment Comparison").Visible = False
       
       Sheets("Payment Comparison").ChartObjects("Payment Comparison2").Visible = True
   
       Else

       Columns("C:E").EntireColumn.Hidden = False
       
       Columns("F").EntireColumn.Hidden = True

       Sheets("Payment Comparison").ChartObjects("Payment Comparison").Visible = True
       
       Sheets("Payment Comparison").ChartObjects("Payment Comparison2").Visible = False
   
       End If
         
   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("M1"), AllowFormattingCells:=True
    
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cancel = True

   ActiveSheet.PageSetup.CenterFooter = Range("C23").Text
   
If Target.Count > 1 Then Exit Sub

Select Case Target.address(0, 0)
   
Case "K15"

   Application.ScreenUpdating = False
         
   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("m1")

   Sheets("Payment Schedule").Visible = True
   
   Range("II5").Select

   Worksheets("Payment Comparison").Visible = xlSheetVeryHidden

   Application.ScreenUpdating = True
       
Case "M15"

   Application.ScreenUpdating = False
         
   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("m1")

   Sheets("Loan Calculator Data").Visible = True
   
   Range("II5").Select

   Worksheets("Payment Comparison").Visible = xlSheetVeryHidden

   Application.ScreenUpdating = True
       
       
'========
'========
   
Case "I15"

   Application.ScreenUpdating = False
         
   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("m1")

   Sheets("Customer").Visible = True
   
   Range("II5").Select

   Worksheets("Payment Comparison").Visible = xlSheetVeryHidden
   
   Application.ScreenUpdating = True
   
       
'========
'========
   
Case "K20"

   Application.ScreenUpdating = False
         
   Range("B15").Value = ""
   Range("B17").Value = ""
   Range("B19").Value = ""
   Range("B21").Value = ""
   Range("B23").Value = ""
   Range("D20").Value = ""
       
   Application.ScreenUpdating = True
  
   
Case "J18"

   Application.ScreenUpdating = False

   ActiveWindow.SelectedSheets.PrintOut Copies:=1
    
   Application.ScreenUpdating = True
   
Case "L18"
       
   Application.ScreenUpdating = False
   
   Application.Run "SendPDFSchedulecharts"
       
   Application.ScreenUpdating = True
   
End Select
End Sub

i have tried a few different ways but with this current coding when i go to the worksheet column f is visible as well as d:e and the both charts are visible, so obviously i am doing something wrong for the macro not to do anything....

any ideas>

Tony

Answer
Tony,

you need to remove or comment out your On Error Resume Next statements.   Then see if when the code runs you get an error.  If so, fix the error.

If you don't and nothing happens, then good change the Events are disabled or all code is disabled.  

If some events are working, then that means the code isn't disabled.  

this line:
ActiveSheet.unprotect Password:=Sheet1.Range("M1"), AllowFormattingCells:=True


should raise an error because the Unprotect command only takes one argument

Activesheet.Unprotect Password:=Sheet1.Range("M1")

so if that raises an error which is suppressed, then the sheet is never unprotected than all the other commands would be inhibited.  

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.