Make all variable in Array = Empty each click of a Button on Userf

[RyanH]

The following code is an example of what my actual code is (my actual code is
too lengthy to post). myArray will always contain 4 variables. I need to
reset each variable in myArray equal to 0 each time Sub Test() is called. As
you can see I have to declare the variables in the Declarations section to
make them available to both procedures. By doing this unwanted text is
copied from Sheet1 to Sheet2.

For example, in my UserForm say the user fires the Test_Click Event and
CheckBox1 = True and ComboBox.ListIndex = 1, thus var1=100, var2=0, var3=300,
var4=0 and the UserFrom remains Loaded. So far so good! The problem happens
when the user may fire the Test_Click Event again when CheckBox1 = False and
ComboBox.ListIndex = 3, thus var1=100, var2=200, var3=300, var4=400. This is
no good, because now all four variables have data other than = 0. The
variables should show like this: var1=0, var2=200, var3=0, var4=400.

So how do I reset the variables values without destroying myArray? I don't
think I could use Erase or ReDim, because this would destroy the Array,
right? I thought about just looping thru myArray and make each variable =
Empty, but I figured there would be a more efficient way. I hope this is
clear.


Option Explicit

Dim var1 As Single
Dim var2 As Single
Dim var3 As Single
Dim var4 As Single

Private Sub cmbTest_Click()

Dim myArray As Variant

' array of row numbers determined by procedure cmbCalculate_Click
myArray = Array(var1, var2, var3, var4)

' calculate variables in myArray
Call cmbCalculate_Click

' adds text from Sheet1 to Sheet2
InputRow = 6
For i = LBound(myArray) To UBound(myArray)
If Not myArray(i) = 0 Then
Sheets("Sheet1").Range("A" & InputRow & "" &
InputRow).Value = _
Sheets("Sheet2").Range("A" & myArray(i) & "" &
myArray(i)).Value
InputRow = InputRow + 2
End If
Next i

End Sub

Private cmbCalculate_Click()

If CheckBox1 = True Then
var1 = 100
Else
var2 = 200
End If

If ComboBox.ListIndex = 1 Then
var3 = 300
Else
var4 = 400
End If

End Sub

 

[Leith Ross]

The following code is an example of what my actual code is (my actual code is
too lengthy to post). myArray will always contain 4 variables. I need to
reset each variable in myArray equal to 0 each time Sub Test() is called. As
you can see I have to declare the variables in the Declarations section to
make them available to both procedures. By doing this unwanted text is
copied from Sheet1 to Sheet2.

For example, in my UserForm say the user fires the Test_Click Event and
CheckBox1 = True and ComboBox.ListIndex = 1, thus var1=100, var2=0, var3=300,
var4=0 and the UserFrom remains Loaded. So far so good! The problem happens
when the user may fire the Test_Click Event again when CheckBox1 = False and
ComboBox.ListIndex = 3, thus var1=100, var2=200, var3=300, var4=400. This is
no good, because now all four variables have data other than = 0. The
variables should show like this: var1=0, var2=200, var3=0, var4=400.

So how do I reset the variables values without destroying myArray? I don't
think I could use Erase or ReDim, because this would destroy the Array,
right? I thought about just looping thru myArray and make each variable =
Empty, but I figured there would be a more efficient way. I hope this is
clear.

Option Explicit

Dim var1 As Single
Dim var2 As Single
Dim var3 As Single
Dim var4 As Single

Private Sub cmbTest_Click()

Dim myArray As Variant

' array of row numbers determined by procedure cmbCalculate_Click
myArray = Array(var1, var2, var3, var4)

' calculate variables in myArray
Call cmbCalculate_Click

' adds text from Sheet1 to Sheet2
InputRow = 6
For i = LBound(myArray) To UBound(myArray)
If Not myArray(i) = 0 Then
Sheets("Sheet1").Range("A" & InputRow & "" &
InputRow).Value = _
Sheets("Sheet2").Range("A" & myArray(i) & "" &
myArray(i)).Value
InputRow = InputRow + 2
End If
Next i

End Sub

Private cmbCalculate_Click()

If CheckBox1 = True Then
var1 = 100
Else
var2 = 200
End If

If ComboBox.ListIndex = 1 Then
var3 = 300
Else
var4 = 400
End If

End Sub

Hello Ryan,

Erase will set all the elements of a numeric array to zero and a
string array's elements to an empty string "". The array will not be
destroyed.

Sincerely,
Leith Ross

 

[StumpedAgain]

Just a thought, may not work. How about trying:

Private cmbCalculate_Click()

If CheckBox1 = True Then
var1 = 100
var2 = 0
Else
var1 = 0
var2 = 200
End If

If ComboBox.ListIndex = 1 Then
var4 = 0
var3 = 300
Else
var3 = 0
var4 = 400
End If

End Sub

Maybe I didn't understand the problem all the way?

 

[RyanH]

I may not be coding this right. This is what I have and I am getting an
error 'Subscript out of Range' indicated below. Also, if I know myArray
always contains variables with Data Type "Single" and always has an UBound or
13, why can't I use: Dim myArray(13) As Single?


Dim myArray As Variant

' array of row numbers determined by procedure cmbCalculate_Click
myArray = Array(var1, var2, var3, var4)

Erase myArray

' calculate variables in myArray
Call cmbCalculate_Click

' adds text from Sheet1 to Sheet2
InputRow = 6

ERROR=> For i = LBound(myArray) To UBound(myArray)
If Not myArray(i) = 0 Then
Sheets("Sheet1").Range("A" & InputRow & "" &
InputRow).Value = _
Sheets("Sheet2").Range("A" & myArray(i) & "" &
myArray(i)).Value
InputRow = InputRow + 2
End If
Next i

End Sub

Private cmbCalculate_Click()

If CheckBox1 = True Then
var1 = 100
Else
var2 = 200
End If

If ComboBox.ListIndex = 1 Then
var3 = 300
Else
var4 = 400
End If
End Sub

 

[RyanH]

This should work for you! I took the liberty of highlighting the unlocked
cells yellow. This will maximize readilbilty. If you don't want that then
just delete it out of the code.

Note: The sheet has to be protected for the Lock Property of the cells to
be active.

Sub LockDates()

Const wksName As String = "Sheet1"
Dim cell As Range

Sheets(wksName).Unprotect Password:=""
For Each cell In Sheets(wksName).UsedRange
If cell.Value = Date Then
cell.Locked = False
Else
cell.Locked = True
End If
Next cell
Sheets(wksName).Protect Password:=""

End Sub

If this helps please click "Yes" below.

 

[RyanH]

I have thought about something like that, but the code gets really messy that
way. I was hoping to find something more efficient.