Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

How may actionable items can you have under an ElseIf Statement

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Martin888 - 21 Nov 2007 18:25 GMT
The following code works but self interrupts and does not close and
consequently the worksheet freezes and is only released by pressing the Esc
key. Any suggestions?

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

Set w = Worksheets("Run1")
Set curcell = Worksheets("Run1").Range("G18")
Set curcell2 = Worksheets("Run1").Range("H18")
On Error GoTo Endit
       If curcell <= 0.3 Then
           Range("H18") = 4
           Range("M25") = 6.7
           Range("M26") = 25
           Range("M27") = 75
           Range("M28") = 93.3
           Range("M29") = 0
           Range("M30") = 0
           Range("M31") = 0
           Range("M32") = 0
           Range("M33") = 0
           Range("M34") = 0
           Range("M35") = 0
           Range("M36") = 0
         
           
           
       ElseIf curcell > 0.3 And curcell <= 0.7 Then
           Range("H18") = 6
           Range("M25") = 4.4
           Range("M26") = 14.6
           Range("M27") = 29.6
           Range("M28") = 70.5
           Range("M29") = 85.4
           Range("M30") = 95.6
           Range("M31") = 0
           Range("M32") = 0
           Range("M33") = 0
           Range("M34") = 0
           Range("M35") = 0
           Range("M36") = 0
         
       ElseIf curcell > 0.7 And curcell <= 1 Then
           Range("H18") = 8
           Range("M25") = 3.2
           Range("M26") = 10.8
           Range("M27") = 19.4
           Range("M28") = 32.3
           Range("M29") = 67.7
           Range("M30") = 80.6
           Range("M31") = 89.5
           Range("M32") = 96.8
           Range("M33") = 0
           Range("M34") = 0
           Range("M35") = 0
           Range("M36") = 0
       
       ElseIf curcell > 1 Then
           Range("H18") = 12
           Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8
           
           Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") =
35.6 And Range("M31") = 64.4
           
           
           
           Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") =
88.2 And Range("M35") = 93.3 And Range("M36") = 97.9
           
           
           
           
           End If
 

Exit Sub
Endit:
End Sub
FSt1 - 21 Nov 2007 18:56 GMT
hi
you are missing the value property of the range object......
If curcell.value <= 0.3 Then
           Range("H18").value = 4
           Range("M25").value = 6.7
           Range("M26").vlaue = 25
           ect.ect
           ect.ect

Regards
FSt1

> The following code works but self interrupts and does not close and
> consequently the worksheet freezes and is only released by pressing the Esc
[quoted text clipped - 75 lines]
> Endit:
> End Sub
Martin888 - 21 Nov 2007 19:21 GMT
Hi,
thankyou for the suggestion unfortunatley the problem continues unabated
Cheers Martin888

> hi
> you are missing the value property of the range object......
[quoted text clipped - 87 lines]
> > Endit:
> > End Sub
Jim Thomlinson - 21 Nov 2007 19:47 GMT
As a best guess without actually running your code you are getting into a
recursive loop. In your change event you are changing values which will
initiate a cnage event that will ... You need to disable events while you are
making the changes... Note that I added in the .value at the end of curcell.
While value is the default it never hurts to be explicit. Also throughout
your code you have Range("??"). You are best off to explicitly declare which
sheet they are referencing.

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

Set w = Worksheets("Run1")
Set curcell = w .Range("G18")
Set curcell2 = w .Range("H18")
On Error GoTo Endit
application.enableevents = false
       If curcell.value <= 0.3 Then
           Range("H18") = 4
           Range("M25") = 6.7
           Range("M26") = 25
           Range("M27") = 75
           Range("M28") = 93.3
           Range("M29") = 0
           Range("M30") = 0
           Range("M31") = 0
           Range("M32") = 0
           Range("M33") = 0
           Range("M34") = 0
           Range("M35") = 0
           Range("M36") = 0
         
           
           
       ElseIf curcell.value  > 0.3 And curcell.value  <= 0.7 Then
           Range("H18") = 6
           Range("M25") = 4.4
           Range("M26") = 14.6
           Range("M27") = 29.6
           Range("M28") = 70.5
           Range("M29") = 85.4
           Range("M30") = 95.6
           Range("M31") = 0
           Range("M32") = 0
           Range("M33") = 0
           Range("M34") = 0
           Range("M35") = 0
           Range("M36") = 0
         
       ElseIf curcell.value  > 0.7 And curcell.value  <= 1 Then
           Range("H18") = 8
           Range("M25") = 3.2
           Range("M26") = 10.8
           Range("M27") = 19.4
           Range("M28") = 32.3
           Range("M29") = 67.7
           Range("M30") = 80.6
           Range("M31") = 89.5
           Range("M32") = 96.8
           Range("M33") = 0
           Range("M34") = 0
           Range("M35") = 0
           Range("M36") = 0
       
       ElseIf curcell.value  > 1 Then
           Range("H18") = 12
           Range("M25") = 2.1 And Range("M26") = 6.7 And Range("M27") = 11.8
           
           Range("M28") = 17.7 And Range("M29") = 25 And Range("M30") =
35.6 And Range("M31") = 64.4

           Range("M32") = 75 And Range("M33") = 82.3 And Range("M34") =
88.2 And Range("M35") = 93.3 And Range("M36") = 97.9
           
           End If
application.enableevents = true

Exit Sub
Endit:
application.enableevents = true
End Sub
Signature

HTH...

Jim Thomlinson

> Hi,
> thankyou for the suggestion unfortunatley the problem continues unabated
[quoted text clipped - 91 lines]
> > > Endit:
> > > End Sub
Martin888 - 21 Nov 2007 20:21 GMT
Good morning Jim
Many thanks code now running correctly
Cheers Martin

> As a best guess without actually running your code you are getting into a
> recursive loop. In your change event you are changing values which will
[quoted text clipped - 172 lines]
> > > > Endit:
> > > > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.