VBA help to stop one code from affecting another

[10004991]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C14" '<== change to suit


On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
ActiveSheet.Unprotect Password:="crazyhorse"
Rows("21:137").Select
Selection.EntireRow.Hidden = False
With ActiveSheet
For i = 21 To 137
If Cells(i, 3) = "NOT APPLICABLE" Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next
End With
Range("C14:E14").Select
Columns("C:O").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 3 To 15
If Cells(21, i) = "NOT APPLICABLE" Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
End With
Range("C14").Select
ActiveSheet.Protect Password:="crazyhorse"

End With
End If


ws_exit:
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)








Dim fullRange As Range

Set fullRange = Worksheets("DR-976A-English").Range("C21:O74")

For Each cell In fullRange
If Cell.Value Is > 550 Then
cell.Interior.ColorIndex = 3
elseif Cell.Value Is < 450 Then
cell.Interior.ColorIndex = 3

Else

cell.Interior.ColorIndex = xlNone

Next
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------
Hello

Second code is effecting the first code

First code works as is and changes the displayed arrays so that data can be entered. This is controlled by a drop down list at cell C14

Second code is to highlight cells less than 450 and greater than 550 red. It works except is causes the first code to stop functioning properly. The first code gets stuck on whatever was selected within cell C14 so that the array displayed is not correct.


For example:
Dropdown selection IP36 displays a range of C21:H32
If I try to change to CA84 which is a range of C21:I44 the correct formatting for CA84 appears but only the range C21:H32


Would love some advice!
Thanks!

[Kenneth Hobs]

Welcome to the forum!

Code first without Select. e.g.

'Rows("21:137").Select
'Selection.EntireRow.Hidden = False
Rows("21:137").EntireRow.Hidden = False

You do not need ActiveSheet either, providing that you use my tip.

Tip: There is no need for Protect/Unprotect unless you want user interaction. For code changes, use this sort of thing in ThisWorbook object:

Private Sub Workbook_Open()  Dim ws As Worksheet
  For Each ws In Worksheets
    ws.Protect "Ken", UserInterfaceOnly:=True
  Next ws End Sub

Or for just that one worksheet:

Private Sub Workbook_Open()  Dim ws As Worksheet
  Worksheets("DR-976A-English").Protect "crazyhorse", UserInterfaceOnly:=True
End Sub

[SamT]

There isn't any EnablelEvents code in the second sub. Disabling events in the second will prevent the first sub from triggering.

[10004991]

Thank you that worked!

I'm quite new to macros so still getting the hang of things.
Do you have any suggestions on understanding them?

[10004991]

Thank you that worked!

Still new to macros so I'm getting the hang of things.
Do you have any suggestions for understanding them better?

[SamT]

Do you have any suggestions for understanding them better?

First: There's Macros, and then there are Procedures.

Macros are recorded by the Macro Recorder and include everything, used or not.

Procedures are written by VBA Programmers and only use what is necessary. also Functions are Procedures, and the Macro Recorder can't do Functions.

As Kenneth implied, Macros can be edited into Procedures.

Macros really require no learning, except to practice what you want to do, so that, when you record the Macro you make no false moves.

Study Code here and on our KnowledgeBase page to see real Procedures in action.

IMO, the VBIDE in Office 2000 to Office 2003 is the best since it has builtin Help and you won't have to spend bandwidth browsing the internet. EBay sells OEM CDs for ~$10 US. Install the early Office and fully update it before installing any later versions. I believe that all versions have the DLL explorer (F2) that is very handy for seeing what is available to your code.

With any version, go to the VBA Menu Tools >> Options >> Editor tab and check everything in the "Code Settings" Frame. On the General Tab, check whatever strikes your fancy, but at least "Error Trapping":=Break on Unhandled Errors, and "Compiile":=Both boxes.

[Kenneth Hobs]

My advice is similar to SamT's.

1. Go beyond the macro recorder. IF you can record, you are ready for this step. http://www.tushar-mehta.com/excel/vb...rder/index.htm

2. Be organized and make comments in code. I like to comment were I copied code from or got the idea from. For the organized part, I put gems of code into a file and save it to a folder with a category. e.g.
PDFs folder name would have examples of ways to make pdfs. Email\Outlook would have outlook code examples.
a. For some snippets of code, I create a Module in my personal workbook and rename the module say mPDF, mOutlook, etc. Your personal workbook is created after you record your first macro. You can add more of these hidden workbooks but fewer is usually better. I do back mine up every so often as hard drives will fail at some point.
b. Practice structured coding concepts. What I mean is to indent (tab). I set my VBE options to indent 2 spaces, not 4. We keep that structure when we post code. We use html code tags in this forum. In this one, click the # icon on reply toolbar to insert the tags. e.g.

Sub Test()  
Dim i As Integer
  
  For i = 1 To 10 Step 2
    Select Case i
      Case 1
        Debug.Print "Case 1"
      Case 2
        Debug.Print "Case 2"
      Case 3
        Debug.Print "Case 3"
      Case Else
        Debug.Print i
    End Select
  Next i
End Sub

c. Always use Option Explicit at top of Modules and such. With the option set in the VBE (Visual Basic Editor), this requires you to Dim all variables. This also keeps your variable capitalization consistent.

3. Helping others helps you learn more. Try to participate in the forum and respond with your proposed solution. If others do it different than you, copy their solution and next time, you can build on that knowledge. Even if you do not post your solution, you can follow the threads of interest. IF you did (2) then just copy the link and post it to solve the same type of problem Howsoever, if you do and the link is from another forum, it is best to also include the code so that the solution stays in this forum. I only post a few links like the one in (1).

4. The kb can be found at: http://www.vbaexpress.com/kb/default.php

5. In the VBE View menu, select Immediate Window. This lets you view the result of a Debug.Print line. I like to use it direct for quick test. e.g. ?1+1
The question mark is like Debug.Print 1+1
That and Msgbox are two easy ways to debug a code. There are more advanced debugging methods. For one example, to debug code line by line, put cursor in a Sub and press F8 and each line after F8 presses will execute.
a. Always Debug > Compile, before you Run code. This checks for syntax errors. I put that on the VBE menubar since I use it so often. Right click the menubar and Customize. Then drag and drop the options you use most often to the menubar if not there already.