Scrollbars that control values in a Worksheet Change procedure
Posted by Bill on September 29, 2000 8:52 AM
Greetings:
I am writing a procedure that uses a worksheet change event. The value changes made in the individual cells (B77:B79) will then affect several cells that contain UDF's. The worksheet change event works fine when cells B77:B79 are changed manually. My hope is that I could create a scroll bar to change the value in these cells. However, when I try, the values continue to change. My understanding is that the values resulting from the scrollbar change then trigger the worksheet change event creating a loop. I've tried setting application.enableevent=false, but this has no effect. Does anyone have any suggestions. Thanks in advance.
Bill Mason
wcmase@fuse.net
Posted by Ivan Moala on September 29, 2000 6:02 PM
Bill
I'm unsure what exactly you are trying to do as
there are a number of things that look @ odds to one another.
1) The worksheet change event will not be triggered
by a change via a control.
2) Which scrollbar are you using the one from the
Forms or Control tollbox ??
I suspect that you maybe using the Control tollbox
Can you elaborate further and also what code you may have and formulas.
Ivan
Posted by Bill on September 30, 2000 5:58 AM
Ivan:
Here is the code for the worksheet change event:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCells As Range, StockConst As Range, StockVar As Range, NoCols As Integer, i As Integer, ActCol As Integer, x As Integer
Application.ScreenUpdating = False
Set ChangedCells = Range("b77:b79, b82:endcell")
Set SpConst = Range("b77:b79")
Set SpVar = Range("b81:endcell")
If Not Application.Intersect(StockConst, Range(Target.Address)) _
Is Nothing Then
Application.EnableEvents = False
NoCols = Range(cells(82, 2), cells(82, 2).End(xlToRight)).cells.Count
If NoCols = 1 Then x = 2 Else
x = NoCols + 1
For i = 2 To x
spreadcalc (i)
Next i
Application.EnableEvents = True
End If
If Not Application.Intersect(StockVar, Range(Target.Address)) _
Is Nothing Then
ActCol = ActiveCell.Column
spreadcalc (ActCol)
End If
Application.ScreenUpdating = True
End Sub
The two ranges, SpConst and SpVar, supply values to UDFs in other cells. The SpConst range contains variables that are universal(constant) while the SpVar range contains variable that are only applicable for a certain case. The goal here is to recalculate the UDFs by changing any of the variables. As written, the proceedure works when the cells are changed when manually entering a new value into a cell. I would like to limit the cells in SpConst to a range that could be controlled by a scroll bar. I am attempting to use the control toolbox (ActiveX?). What happens is that when the scrollbox changes the value in one of the cells, a loop that refers to each other begins until the limit of the scroll bar range is reached. I hope this clarifies what I'm trying to do but upon re reading this, I wouldn't bet on it. Thanks again for your help.
Bill Mason
Posted by Bill on September 30, 2000 5:58 AM
Ivan:
Here is the code for the worksheet change event:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCells As Range, StockConst As Range, StockVar As Range, NoCols As Integer, i As Integer, ActCol As Integer, x As Integer
Application.ScreenUpdating = False
Set ChangedCells = Range("b77:b79, b82:endcell")
Set SpConst = Range("b77:b79")
Set SpVar = Range("b81:endcell")
If Not Application.Intersect(StockConst, Range(Target.Address)) _
Is Nothing Then
Application.EnableEvents = False
NoCols = Range(cells(82, 2), cells(82, 2).End(xlToRight)).cells.Count
If NoCols = 1 Then x = 2 Else
x = NoCols + 1
For i = 2 To x
spreadcalc (i)
Next i
Application.EnableEvents = True
End If
If Not Application.Intersect(StockVar, Range(Target.Address)) _
Is Nothing Then
ActCol = ActiveCell.Column
spreadcalc (ActCol)
End If
Application.ScreenUpdating = True
End Sub
The two ranges, SpConst and SpVar, supply values to UDFs in other cells. The SpConst range contains variables that are universal(constant) while the SpVar range contains variable that are only applicable for a certain case. The goal here is to recalculate the UDFs by changing any of the variables. As written, the proceedure works when the cells are changed when manually entering a new value into a cell. I would like to limit the cells in SpConst to a range that could be controlled by a scroll bar. I am attempting to use the control toolbox (ActiveX?). What happens is that when the scrollbox changes the value in one of the cells, a loop that refers to each other begins until the limit of the scroll bar range is reached. I hope this clarifies what I'm trying to do but upon re reading this, I wouldn't bet on it. Thanks again for your help.
Bill Mason
