• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use 2 Private sub in one sheet

jack999

Member
I would like to use 2 private sub in one sheet as given below. First one is used for data validation list, and a combobox . Second one is used for Highlight selected Row.
How I can use both these 2 code in one sheet

1st One


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

IInd one


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone

With Target.EntireRow.Interior
.ColorIndex = 37
.Pattern = xlGray25
.PatternColorIndex = 24
End With
End Sub
 
How about like this?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
DataVal (Target)
ColorCells (Target)
End Sub
 
 
Private Sub DataVal(Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
'other stuff
End Sub
Private Sub ColorCells(Target As Range)
Cells.Interior.ColorIndex = xlNone
 
With Target.EntireRow.Interior
    .ColorIndex = 37
    .Pattern = xlGray25
    .PatternColorIndex = 24
End With
End Sub
 
How about like this?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
DataVal (Target)
ColorCells (Target)
End Sub
 
 
Private Sub DataVal(Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
'other stuff
End Sub
Private Sub ColorCells(Target As Range)
Cells.Interior.ColorIndex = xlNone
 
With Target.EntireRow.Interior
    .ColorIndex = 37
    .Pattern = xlGray25
    .PatternColorIndex = 24
End With
End Sub
Thanks sir
 
Hi, jack999!

As a general rule valid for every module or worksheet/workbook class module, procedure names either private or public should be unique, there can't be 2 or more with the same name.

A little deeper in class modules be careful specifically with sub procedures that hold code for object events (as in your 1st post with SelectionChange). Whenever doubtful just go to the menu, Debug, Compile (Alt-D-L) and do a primary syntax check.

Regards!
 
How about like this?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
DataVal (Target)
ColorCells (Target)
End Sub
 
 
Private Sub DataVal(Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
'other stuff
End Sub
Private Sub ColorCells(Target As Range)
Cells.Interior.ColorIndex = xlNone
 
With Target.EntireRow.Interior
    .ColorIndex = 37
    .Pattern = xlGray25
    .PatternColorIndex = 24
End With
End Sub

Sir when I change the code then Color selected row not working. Here is full macro data validation list. I don't know why its not working.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Invoice-Cash-Cheque")

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown

End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp

Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
'====================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True


Can we change it with sheet name
 
Hi ,

I do not understand what you want.

You have listed 3 procedures before repeating the second one , and that too partially.

Are you asking whether you can tailor the following procedure :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

using a sheet name ?

The answer is No ; the above procedure is an event procedure , which means it is automatically triggered from within Excel , when the corresponding event occurs ; in this case , since the procedure is named Worksheet_SelectionChange , when ever the cursor is moved , either by using the cursor keys or by using a mouse ; this procedure is therefore particular to a worksheet ; every worksheet will have its own Worksheet_SelectionChange procedure.

Instead of listing these procedures , can you first describe what you wish to do ?

Narayan
 
I need to use all these macro in one sheet. Can we modify both Private Sub Worksheet_SelectionChange(ByVal Target As Range) suitable to work in one sheet.
Is it possible ?
 
Hi ,

The only point you need to consider is that when one event procedure is automatically triggered by some action in the worksheet / workbook , the code within that event procedure does not again trigger the same or some other event procedure ; from within an event procedure you can call other procedures to do what ever you want , but if you do not prevent one event procedure from performing some action which can trigger either the same or another event procedure , you can create an infinite number of events which will crash Excel.

Normally this happens in the case of a Worksheet_Change event , which is automatically triggered when ever a worksheet cell is changed by a user. Now , if within the Worksheet_Change event procedure , you change a worksheet cell , you are triggering the same procedure once again ; this will repeat ad infinitum , till Excel hangs or crashes.

In your workbook , the 3 procedures you have listed are :

Worksheet_BeforeDoubleClick
Worksheet_SelectionChange
TempCombo_KeyDown

From within any of the 3 procedures , it is very unlikely that you can initiate the first and the third event procedures ; from any of them , you can initiate the second one , since a simple Range.Activate statement anywhere in those 3 procedures can change the cursor location , and therefore trigger the Worksheet_SelectionChange procedure.

To guard against such a thing happening , always have an Application.EnableEvents = False statement at the point where changes are being made , and follow it up with an Application.EnableEvents = True statement after the changes are made.

This is not strictly being followed in the procedures you have listed.

If you can upload the workbook with the procedures and the objects , the required changes can be made , and the file re-uploaded.

Narayan
 
Hi ,

The only point you need to consider is that when one event procedure is automatically triggered by some action in the worksheet / workbook , the code within that event procedure does not again trigger the same or some other event procedure ; from within an event procedure you can call other procedures to do what ever you want , but if you do not prevent one event procedure from performing some action which can trigger either the same or another event procedure , you can create an infinite number of events which will crash Excel.

Normally this happens in the case of a Worksheet_Change event , which is automatically triggered when ever a worksheet cell is changed by a user. Now , if within the Worksheet_Change event procedure , you change a worksheet cell , you are triggering the same procedure once again ; this will repeat ad infinitum , till Excel hangs or crashes.

In your workbook , the 3 procedures you have listed are :

Worksheet_BeforeDoubleClick
Worksheet_SelectionChange
TempCombo_KeyDown

From within any of the 3 procedures , it is very unlikely that you can initiate the first and the third event procedures ; from any of them , you can initiate the second one , since a simple Range.Activate statement anywhere in those 3 procedures can change the cursor location , and therefore trigger the Worksheet_SelectionChange procedure.

To guard against such a thing happening , always have an Application.EnableEvents = False statement at the point where changes are being made , and follow it up with an Application.EnableEvents = True statement after the changes are made.

This is not strictly being followed in the procedures you have listed.

If you can upload the workbook with the procedures and the objects , the required changes can be made , and the file re-uploaded.

Narayan


Sir here with I uploaded a sample file. In Invoice-Cash-Cheque sheet contains Selected Row Highlighted Macro
Requirement sheet contains other macro


I would like to add Selected Row Highlighted Macro in requirement sheet also.
 

Attachments

  • AC-Statement11111.xlsm
    41.1 KB · Views: 4
Hi ,

Can you check your file now ?

Narayan

Now when I double clip Data data validation Combo Box it is showing Run time error and showing this command line Set wsList = Sheets("requirment")
Sir I want only use Highlight color field Macro in in requirement sheet only
 
Hi ,

Sorry , but I did some spelling correction !

Please change the name of the sheet tab to requirement in the code ; otherwise , rename the sheet tab to requirment.

Narayan
 
Hi ,

Sorry , but I did some spelling correction !

Please change the name of the sheet tab to requirement in the code ; otherwise , rename the sheet tab to requirment.

Narayan

Sir I want only use the Macro which Highlight selected row in requirement sheet only.
 
Back
Top