Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Private Sub Worksheet_Change doesn't work?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Private Sub Worksheet_Change doesn't work?
I have this Worsheet_Change code in worksheet Sheet2 (named A1) which used to
work but not anymore.
I've checked the VBA is not in design mode. All other codes in the same
sheet work, except the worksheet change code. What's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

' Change interior color of cells

Dim MyWkSht As Worksheet
Dim HighlighRange1 As Range, HighlightRange2 As Range

Set MyWkSht = ThisWorkbook.Worksheets("A1")

MyWkSht.Range("A31").Interior.ColorIndex = 36

Set HighlightRange1 =
MyWkSht.Range("E31:T31,U31:AC40,AD31:AG40,AJ31:AL4 0,U19:AC19,AD19:AG28,AJ19:AL28,R43:T43,U43:AC52,AD 43:AG52,AJ43:AL52,A32")

If MyWkSht.Range("A31").Value < "" Then
HighlightRange1.Interior.ColorIndex = 36

Else
HighlightRange1.Interior.ColorIndex = xlNone

End If

For n = 1 To 9

Set r1 = MyWkSht.Range("E" & n + 31 & ":" & "R" & n + 31)
Set r2 = MyWkSht.Range("U" & n + 19)
Set r3 = MyWkSht.Range("R" & n + 43)
Set r4 = MyWkSht.Range("A" & n + 32)
Set HighlightRange2 = Union(r1, r2, r3, r4)

If MyWkSht.Range("A" & n + 31).Value < "" Then
HighlightRange2.Interior.ColorIndex = 36

Else
HighlightRange2.Interior.ColorIndex = xlNone

End If

Next n

MyWkSht.Range("A41").Interior.ColorIndex = xlNone

' Toggle the visibilities of the command buttons on Sheet1 and Sheet2

If MyWkSht.Range("AW8").Value = "" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
cbInputB1.Enabled = False
cbOutput.Enabled = False
cbOutput.Left = 216

ElseIf MyWkSht.Range("AW8").Value = "Yes" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
Sheet1.cbInputB2.Visible = False
Sheet1.cbInputB3.Visible = False
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 216
Sheet1.cbOutput.Left = 216

Else
cbInputB2.Visible = True
cbInputB3.Visible = True
Sheet1.cbInputB2.Visible = True
Sheet1.cbInputB3.Visible = True
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 432
Sheet1.cbOutput.Left = 432

End If

End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Private Sub Worksheet_Change doesn't work?
Dim HighlighRange1 As Range

You have a typo in the posted version above. Don't know if it is in the
original or not.
That would not cause the code not to execute.

What exactly do you mean by don't work. Do you get an error message or just
no results. If it is just no results, you need to check your criteria cells
for your If statements to make sure the do or do not contain what you think
they should. Then step through the procedure by setting a breakpoint after
the first step then check one step at a time to see where it fails.


"Sam Kuo" wrote:

I have this Worsheet_Change code in worksheet Sheet2 (named A1) which used to
work but not anymore.
I've checked the VBA is not in design mode. All other codes in the same
sheet work, except the worksheet change code. What's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

' Change interior color of cells

Dim MyWkSht As Worksheet
Dim HighlighRange1 As Range, HighlightRange2 As Range

Set MyWkSht = ThisWorkbook.Worksheets("A1")

MyWkSht.Range("A31").Interior.ColorIndex = 36

Set HighlightRange1 =
MyWkSht.Range("E31:T31,U31:AC40,AD31:AG40,AJ31:AL4 0,U19:AC19,AD19:AG28,AJ19:AL28,R43:T43,U43:AC52,AD 43:AG52,AJ43:AL52,A32")

If MyWkSht.Range("A31").Value < "" Then
HighlightRange1.Interior.ColorIndex = 36

Else
HighlightRange1.Interior.ColorIndex = xlNone

End If

For n = 1 To 9

Set r1 = MyWkSht.Range("E" & n + 31 & ":" & "R" & n + 31)
Set r2 = MyWkSht.Range("U" & n + 19)
Set r3 = MyWkSht.Range("R" & n + 43)
Set r4 = MyWkSht.Range("A" & n + 32)
Set HighlightRange2 = Union(r1, r2, r3, r4)

If MyWkSht.Range("A" & n + 31).Value < "" Then
HighlightRange2.Interior.ColorIndex = 36

Else
HighlightRange2.Interior.ColorIndex = xlNone

End If

Next n

MyWkSht.Range("A41").Interior.ColorIndex = xlNone

' Toggle the visibilities of the command buttons on Sheet1 and Sheet2

If MyWkSht.Range("AW8").Value = "" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
cbInputB1.Enabled = False
cbOutput.Enabled = False
cbOutput.Left = 216

ElseIf MyWkSht.Range("AW8").Value = "Yes" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
Sheet1.cbInputB2.Visible = False
Sheet1.cbInputB3.Visible = False
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 216
Sheet1.cbOutput.Left = 216

Else
cbInputB2.Visible = True
cbInputB3.Visible = True
Sheet1.cbInputB2.Visible = True
Sheet1.cbInputB3.Visible = True
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 432
Sheet1.cbOutput.Left = 432

End If

End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Private Sub Worksheet_Change doesn't work?
Hi JLGWhiz

Thanks for picking up the typo.
The worksheet change code in that particular worksheet just doesn't execute
when I posted this question, but somehow it's working again after I reopen
the workbook. I guess it might be the code changes I made to other linked
worksheet at the same time that confuse Excel. But thank god it's all good
now :-)

Thanks for your suggestion.

Sam

"JLGWhiz" wrote:

Dim HighlighRange1 As Range

You have a typo in the posted version above. Don't know if it is in the
original or not.
That would not cause the code not to execute.

What exactly do you mean by don't work. Do you get an error message or just
no results. If it is just no results, you need to check your criteria cells
for your If statements to make sure the do or do not contain what you think
they should. Then step through the procedure by setting a breakpoint after
the first step then check one step at a time to see where it fails.


"Sam Kuo" wrote:

I have this Worsheet_Change code in worksheet Sheet2 (named A1) which used to
work but not anymore.
I've checked the VBA is not in design mode. All other codes in the same
sheet work, except the worksheet change code. What's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

' Change interior color of cells

Dim MyWkSht As Worksheet
Dim HighlighRange1 As Range, HighlightRange2 As Range

Set MyWkSht = ThisWorkbook.Worksheets("A1")

MyWkSht.Range("A31").Interior.ColorIndex = 36

Set HighlightRange1 =
MyWkSht.Range("E31:T31,U31:AC40,AD31:AG40,AJ31:AL4 0,U19:AC19,AD19:AG28,AJ19:AL28,R43:T43,U43:AC52,AD 43:AG52,AJ43:AL52,A32")

If MyWkSht.Range("A31").Value < "" Then
HighlightRange1.Interior.ColorIndex = 36

Else
HighlightRange1.Interior.ColorIndex = xlNone

End If

For n = 1 To 9

Set r1 = MyWkSht.Range("E" & n + 31 & ":" & "R" & n + 31)
Set r2 = MyWkSht.Range("U" & n + 19)
Set r3 = MyWkSht.Range("R" & n + 43)
Set r4 = MyWkSht.Range("A" & n + 32)
Set HighlightRange2 = Union(r1, r2, r3, r4)

If MyWkSht.Range("A" & n + 31).Value < "" Then
HighlightRange2.Interior.ColorIndex = 36

Else
HighlightRange2.Interior.ColorIndex = xlNone

End If

Next n

MyWkSht.Range("A41").Interior.ColorIndex = xlNone

' Toggle the visibilities of the command buttons on Sheet1 and Sheet2

If MyWkSht.Range("AW8").Value = "" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
cbInputB1.Enabled = False
cbOutput.Enabled = False
cbOutput.Left = 216

ElseIf MyWkSht.Range("AW8").Value = "Yes" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
Sheet1.cbInputB2.Visible = False
Sheet1.cbInputB3.Visible = False
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 216
Sheet1.cbOutput.Left = 216

Else
cbInputB2.Visible = True
cbInputB3.Visible = True
Sheet1.cbInputB2.Visible = True
Sheet1.cbInputB3.Visible = True
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 432
Sheet1.cbOutput.Left = 432

End If

End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple values in Private Sub Worksheet_Change(ByVal Target As R davemon Excel Discussion (Misc queries) 2 September 21st 07 07:40 PM
Control Toolbox and Private Sub Worksheet_Change(ByVal Target As R Toppers Excel Discussion (Misc queries) 0 August 17th 07 02:02 AM
Private Sub Worksheet_Change(ByVal Target As Range) Paige Excel Programming 1 May 17th 07 12:16 AM
Private Sub Worksheet_Change(ByVal Target As Range) pd1234321 Excel Programming 5 December 8th 06 04:11 AM
Private Sub Worksheet_Change(ByVal Target As Range) Arturo Excel Programming 1 May 25th 05 03:32 PM


All times are GMT +1. The time now is 04:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement