Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Error
Maybe...
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B30")) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If Application.EnableEvents = True End Sub mslabbe wrote: Worksheet Change Error I was trying to get a cell change run a macro...got it with this below: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If End If Application.EnableEvents = True End Sub Now, if I want to highlight more then one cell in the range B2:B30, and paste something or delete the contents, I get the old Run-time error '13': Type mismatch. Does anyone know how to get around this? BTW, I was helped getting this formula from this disscussion group...you all are awesome at what you know. Thanks in advance -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Error
Thanks a bunch, as it cleared up the error...however, if I paste values at
the same time in that range, the function does not fire...is there away around that? I did not realize that was happening because of the run-time error I was getting... Thanks again "Dave Peterson" wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B30")) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If Application.EnableEvents = True End Sub mslabbe wrote: Worksheet Change Error I was trying to get a cell change run a macro...got it with this below: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If End If Application.EnableEvents = True End Sub Now, if I want to highlight more then one cell in the range B2:B30, and paste something or delete the contents, I get the old Run-time error '13': Type mismatch. Does anyone know how to get around this? BTW, I was helped getting this formula from this disscussion group...you all are awesome at what you know. Thanks in advance -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Error
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myCell As Range Set myRng = Intersect(Me.Range("b2:b30"), Target) If myRng Is Nothing Then Exit Sub Randomize Application.EnableEvents = False For Each myCell In myRng.Cells If myCell.Value 0 Then myCell(1, 5).Value = Rnd myCell(1, 7).Value = Rnd End If If myCell.Value = 0 Then myCell(1, 5).Value = 0 myCell(1, 7).Value = 0 End If Next myCell Application.EnableEvents = True End Sub I wasn't sure what you were doing--are Randomize and Randomize2 your own functions? If they are, you'll have to modify this code--I changed it. You may want to stay away from naming your function Randomize, since VBA has its own Randomize statement. mslabbe wrote: Thanks a bunch, as it cleared up the error...however, if I paste values at the same time in that range, the function does not fire...is there away around that? I did not realize that was happening because of the run-time error I was getting... Thanks again "Dave Peterson" wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B30")) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If Application.EnableEvents = True End Sub mslabbe wrote: Worksheet Change Error I was trying to get a cell change run a macro...got it with this below: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If End If Application.EnableEvents = True End Sub Now, if I want to highlight more then one cell in the range B2:B30, and paste something or delete the contents, I get the old Run-time error '13': Type mismatch. Does anyone know how to get around this? BTW, I was helped getting this formula from this disscussion group...you all are awesome at what you know. Thanks in advance -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Error
Dave...that took me a bit, but man, your good...thanks. I just had two
different randomizer numbering functions I took out "Randomize" And add these two: myCell(1, 5).Value = Int(Rnd * 10) + 1 myCell(1, 7).Value = Int(Rnd * 20) + 1 so, if anyone else was reading this and was interested, here is what Dave got me to: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myCell As Range Set myRng = Intersect(Me.Range("b2:b30"), Target) If myRng Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In myRng.Cells If myCell.Value 0 Then myCell(1, 5).Value = Int(Rnd * 10) + 1 myCell(1, 7).Value = Int(Rnd * 20) + 1 End If If myCell.Value = 0 Then myCell(1, 5).Value = 0 myCell(1, 7).Value = 0 End If Next myCell Application.EnableEvents = True End Sub Again, thanks for your help! Cheers "Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myCell As Range Set myRng = Intersect(Me.Range("b2:b30"), Target) If myRng Is Nothing Then Exit Sub Randomize Application.EnableEvents = False For Each myCell In myRng.Cells If myCell.Value 0 Then myCell(1, 5).Value = Rnd myCell(1, 7).Value = Rnd End If If myCell.Value = 0 Then myCell(1, 5).Value = 0 myCell(1, 7).Value = 0 End If Next myCell Application.EnableEvents = True End Sub I wasn't sure what you were doing--are Randomize and Randomize2 your own functions? If they are, you'll have to modify this code--I changed it. You may want to stay away from naming your function Randomize, since VBA has its own Randomize statement. mslabbe wrote: Thanks a bunch, as it cleared up the error...however, if I paste values at the same time in that range, the function does not fire...is there away around that? I did not realize that was happening because of the run-time error I was getting... Thanks again "Dave Peterson" wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B30")) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If Application.EnableEvents = True End Sub mslabbe wrote: Worksheet Change Error I was trying to get a cell change run a macro...got it with this below: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then If Target.Value 0 Then Target(1, 5).Value = Randomize(Rnd) Target(1, 7).Value = Randomize2(Rnd) End If If Target.Value = 0 Then Target(1, 5).Value = 0 Target(1, 7).Value = 0 End If End If Application.EnableEvents = True End Sub Now, if I want to highlight more then one cell in the range B2:B30, and paste something or delete the contents, I get the old Run-time error '13': Type mismatch. Does anyone know how to get around this? BTW, I was helped getting this formula from this disscussion group...you all are awesome at what you know. Thanks in advance -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object hyperlink produces "Reference is not valid" error after worksheet name change | Excel Programming | |||
424 error on a Worksheet Change Macro | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
worksheet change event error events disabled | Excel Programming | |||
Sorting a 2 column list with VBA using Worksheet change produces 1004 error | Excel Programming |