Help understanding why this code wont work :(

[Legacy 237453]

Hi..

Can somebody please tell me why this code wont work.... I want it so that when i select cell J:7 the text in cells L:15 , L:17 & L:19 change

Private Sub worksheet_Selectionchange(ByVal Target As Range)
    Select Case Target.Address
    Case Is = "$J$7"
        Range("L15").Value = "No Callout nessasary"
        Range("L17").Value = "Multiple instructions please see values workbook"
        Range("L19").Value = "Processed data via SAP port"
    End Select
End Sub

Please can somebody explain what im doing wrong in a beginneer lol

Thanks as always!
Craig

 

[Gary's Student]

This seems to work:

Private Sub worksheet_Selectionchange(ByVal Target As Range)
Application.EnableEvents = False
    Select Case Target.Address
    Case Is = "$J$7"
        Range("L15").Value = "No Callout nessasary"
        Range("L17").Value = "Multiple instructions please see values workbook"
        Range("L19").Value = "Processed data via SAP port"
    End Select
Application.EnableEvents = True
End Sub

 

[Legacy 237453]

Hi

Thanks for the reply...

Ive copyied it but still it doesnt seem to work? any other suggestions?

Thanks
Craig

 

[Legacy 237453]

Hi

Ok i can see why its not working..... for some reason if the case cell J:7 is merged and centered then it wont work.... ? is there a way to get round this otherwise i will have to change the whold layout of the workbook

Thanks
Craig

 

[Gary's Student]

What is J7 merged with??

 

[Legacy 237453]

merged with cells K7 , L7 & M7

Thanks

 

[Gary's Student]

Private Sub worksheet_Selectionchange(ByVal Target As Range)
Application.EnableEvents = False
    Select Case Target.Address
    [COLOR=#8b4513]Case Is = "$J$7:$M$7"[/COLOR]
        Range("L15").Value = "No Callout nessasary"
        Range("L17").Value = "Multiple instructions please see values workbook"
        Range("L19").Value = "Processed data via SAP port"
    End Select
Application.EnableEvents = True
End Sub

 

[Legacy 237453]

Perfect! Thanks so much for your help! I love this fourm lol

 

[Legacy 237453]

Hi again.... I Wonder if theres 1 more thing you can help me with. Ideally i want the word "not" to be in bold and highlighted in Red.

Case Is = "$N$13:$Q$13"
Range("L15").Value = "No Callout nessasary"
Range("L17").Value = "Extraction BW - Weekly Transactional Data"
Range("L19").Value = "Attention, In case of failure, this job can not be launched again. Upon failure Copy & Paste job log in to Applix & send over to Exploit Geti"
End Select

Thanks alot
Craig

 

[Michael M]

Hey Craig
Firstly, try to avoid the use of merged cells, especially in VBA, they will give you nightmares eventually !!!
Instead, select the range to be merged and try using, Format / Cells / Alignment / Horizontal alignment....and select Center Across Selection from the drop down.
For your other problem try

Private Sub worksheet_Selectionchange(ByVal Target As Range)
    Select Case Target.Address
Case Is = "$N$13:$Q$13"
Range("L15").Value = "No Callout nessasary"
Range("L17").Value = "Extraction BW - Weekly Transactional Data"
Range("L19").Value = "Attention, In case of failure, this job can not be launched again. Upon failure Copy & Paste job log in to Applix & send over to Exploit Geti"
End Select
    With Range("L19").Characters(Start:=45, Length:=3).Font
        .FontStyle = "Bold"
        .Size = 14
    End With
End Sub