Help understanding why this code wont work :(

L

Legacy 237453

Guest
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

Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,015
This seems to work:

Code:
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
 
L

Legacy 237453

Guest
Hi

Thanks for the reply...

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

Thanks
Craig
 
L

Legacy 237453

Guest
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

Well-known Member
Joined
Aug 4, 2012
Messages
1,015

ADVERTISEMENT

What is J7 merged with??
 

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,015

ADVERTISEMENT

Code:
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
 
Last edited:
L

Legacy 237453

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

Legacy 237453

Guest
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,174
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,033
Messages
5,639,655
Members
417,102
Latest member
bcselect

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top