Chaange event to work on every sheet

[im_kaushal]

Hi All,

The below mentioned event code changes the changed cell value to bold. I want this to be applicable to all the worksheets available in the workbook. I know i can paste this code in all the worksheets, but just want to know is there a better way of making it applicable in all the worksheets, specially in a situation where my workbook has around 50 sheets.

macro which changes the cell value to bold is

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.Bold = True
End Sub

Regards, Kaushal

 

[repairman615]

Try placing into ThisWorkBook module.

 

[im_kaushal]

No. then it doesn't work.

Rgds, K

 

[pgc01]

Hi Kaushal

Did you use the the Workbook_SheetChange event?

 

[Rasm]

This should work

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Stop
End Sub

 

[pgc01]

Rasm, I guess you meant to use the SheetChange event and not the SheetSelectionChange event.


Kaushal, paste in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Font.Bold = True
End Sub

 

[Rasm]

sry-me bad - yes

 

[im_kaushal]

Thanks everyone it works absolutely fine..

Can somebody please explain how this event work for my understanding

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

I need to understand ByVal, Sh , etc. what does this means ?


Many thanks in advance
Regards, Kaushal

 

[Rasm]

you can set a variable to be ByRef or ByVal - means if they can be changed inside the sub/function.

The varibles passed to the sub/function can be read by you - i.e. a target will tell which cell is active i.e. Target.adress returns someting like "A1" (if you clicked cell A1 or Target.row would return 1.

You should insert a breakpoint into the code (press inside the narrow grey bar next to the code - bar is vertical) - also turn on i.e a Local watch point - now you can see the value of all the varaibles applicable to that sub/function when the program stops at the breakpoint.

 

[SydneyGeek]

Thanks everyone it works absolutely fine..

Can somebody please explain how this event work for my understanding

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

I need to understand ByVal, Sh , etc. what does this means ?


Many thanks in advance
Regards, Kaushal

Sh is a variable representing the worksheet
Target represents the selected cell(s) on that sheet
When you use parameters in a sub/function you can pass them ByRef (the default) or ByVal.

Paste this into a new module and run Procedure1, to see what ByRef does to the 4th message. Then change to ByVal and run it again.

Sub Procedure1()
    Dim strMessage As String
    
    strMessage = "Born in Proc1"
    
    MsgBox "1: " & strMessage
    
    Procedure2 strMessage
    
    MsgBox "4: " & strMessage
End Sub

Sub Procedure2(ByRef strMessage As String)
    MsgBox "2: " & strMessage
    
    strMessage = "Changed in Proc2"
    
    MsgBox "3: " & strMessage
End Sub

Notice that when you use ByVal, you don't alter the variable in the *original* procedure. Using ByRef you do, because ByRef passes the original memory address of the variable; ByVal only passes the value.

Denis