Excel/formulas

Advertisement


Question
Tom

I did what you mentioned changed the

With salesrpt

to

With wsOrder  

and if i run the macro by itself it works great....does exactly what we want it to do ...but when i place in with the code like below it doesnt execute....

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Sh2 As Worksheet
Dim bk1 As Workbook
Set bk1 = ThisWorkbook
bk1.Activate
Set Sh2 = bk1.Worksheets("salesrpt")

Sh2.unprotect Password:=Sheet1.Range("M1")

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub

If Target.Column = 1 Then
If LCase(Target.Value) = "x" Then

   Range("A2:L2").Copy Cells(Target(1).Row, "A")

   End If
   End If
   
   Sh2.Protect Password:=Sheet1.Range("M1")
   
   Application.ScreenUpdating = True
   
   Application.ScreenUpdating = False
   
   Application.Run "sortsalesrpt"

   Application.ScreenUpdating = True

If Target.Address = selectsalesdate Then
If Len(Trim(Target.Value)) = 0 Or LCase(Target.Value) = LCase("select date") Then
Exit Sub
End If

   On Error Resume Next
   ActiveSheet.unprotect Password:=Sheet1.Range("M1")
   
   'Application.Run "Filter"

   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("M1")
   
End If
End Sub

or i also tried the following on case

K1 = selectsalesdate as a defined name

and i have code in J1 that if K1 has select date, then 1, if not then 2 which should execute the macro.

still doesnt work  any idea

Case "K1"

   Application.ScreenUpdating = False
   
   If (Range("J1").Value) = 1 Then

   Exit Sub
   
   Application.Run "Filter"
   
   End If

Answer
Tony,


this is the original code you sent me with a msgbox added just before it would execute the Filter macro

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Sh2 As Worksheet
Dim bk1 As Workbook
Set bk1 = ThisWorkbook
bk1.Activate
Set Sh2 = bk1.Worksheets("salesrpt")

Sh2.unprotect Password:=Sheet1.Range("M1")

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub

If Target.Column = 1 Then
If LCase(Target.Value) = "x" Then

  Range("A2:L2").Copy Cells(Target(1).Row, "A")

  End If
  End If
  
  Sh2.Protect Password:=Sheet1.Range("M1")
  
  Application.ScreenUpdating = True
  
  Application.ScreenUpdating = False
  
  Application.Run "sortsalesrpt"

  Application.ScreenUpdating = True

If Target.Address = selectsalesdate Then
If Len(Trim(Target.Value)) = 0 Or LCase(Target.Value) = LCase("select date") Then
Exit Sub
End If

  On Error Resume Next
  ActiveSheet.unprotect Password:=Sheet1.Range("M1")
  
  msgbox "About to run macro named:  Filter"
  Application.Run "Filter"

  On Error Resume Next
  ActiveSheet.Protect Password:=Sheet1.Range("M1")
  
End If
End Sub


**** I also uncommented the Application.Run so it is seen as an executable command rather than a comment.  *****

If you never see the messagebox pop up with the message:  "About to run macro named:  Filter"

then you know that the code is never getting to the execution line and you will have to debug your macro to see why.  If you do see the message and click the button on the msgbox, then your coce should run the filter macro.

--
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.