Calculate Event

[Khalil Handal]

Hi,
I added the code below so as to hide row 30 if A30 i empty (has no value).
A30 has a formula to copy a vakue from another sheet.
The same for rows 31 and 32 and 33

Private Sub Worksheet_Calculate()
Rows("30").Hidden = Range("A30").Value = ""
Rows("31").Hidden = Range("A31").Value = ""
Rows("32").Hidden = Range("A32").Value = ""
Rows("33").Hidden = Range("A33").Value = ""
End Sub

My problem is that I see the "sand clock" (wait sign) and the programm will
freeze. i.e. I cannot do any thing, I have to press Ctrl+Alt+Del. to exit.

Any ideas

 

[Rich/rerat]

Khalil Handal,
You might try:

Private Sub Worksheet_Calculate()

IF Range("A30").Value = "" Then
Rows("30").Hidden
ElseIF Range("A31").Value = "" Then
Rows("31").Hidden
ElseIF Range("A32").Value = "" Then
Rows("32").Hidden
ElseIF Range("A33").Value = "" Then
Rows("33").Hidden

Else
Rows("30").Show
Rows("31").Show
Rows("32").show
Rows("33").Show
EndIf
End Sub


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Hi,
I added the code below so as to hide row 30 if A30 i empty (has no value).
A30 has a formula to copy a vakue from another sheet.
The same for rows 31 and 32 and 33

Private Sub Worksheet_Calculate()
Rows("30").Hidden = Range("A30").Value = ""
Rows("31").Hidden = Range("A31").Value = ""
Rows("32").Hidden = Range("A32").Value = ""
Rows("33").Hidden = Range("A33").Value = ""
End Sub

My problem is that I see the "sand clock" (wait sign) and the programm will
freeze. i.e. I cannot do any thing, I have to press Ctrl+Alt+Del. to exit.

Any ideas

 

[Dave Peterson]

I'd try:

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Rows(30).Hidden = CBool(Me.Range("A30").Value = "")
Me.Rows(31).Hidden = CBool(Me.Range("A31").Value = "")
Me.Rows(32).Hidden = CBool(Me.Range("A32").Value = "")
Me.Rows(33).Hidden = CBool(Me.Range("A33").Value = "")
Application.EnableEvents = True
End Sub

I changed some syntax because I like to qualify my ranges and I find it
documents the code better.

 

[Khalil Handal]

Thank you very much, it worked .

I'd try:

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Rows(30).Hidden = CBool(Me.Range("A30").Value = "")
Me.Rows(31).Hidden = CBool(Me.Range("A31").Value = "")
Me.Rows(32).Hidden = CBool(Me.Range("A32").Value = "")
Me.Rows(33).Hidden = CBool(Me.Range("A33").Value = "")
Application.EnableEvents = True
End Sub

I changed some syntax because I like to qualify my ranges and I find it
documents the code better.