Re: Private and Public Statements

Tech-Archive recommends: Speed Up your PC by fixing your registry


<Spyntek@xxxxxxxxx> wrote in message
news:1176904020.412751.72660@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Good Morning. I am having a bit of a dilemma. I almost have this bit
of code working but I am wondering if I don't have to make one of the
statements Public so it is accessible to all code. Please give me
some insight into this.

Private Sub CommandButton1_Click()
Current_Date

Dim Jobs(22) As String
Dim i, j, K, L As Integer

Jobs(0) = "Bob"
Jobs(1) = "Steve"
Jobs(2) = "Chris"
Jobs(3) = "Jane"
Jobs(4) = "Diane"
Jobs(5) = "Sue"
Jobs(6) = "Margaret"
Jobs(7) = "Donna"
Jobs(8) = "Chuck"
Jobs(9) = "John"
Jobs(10) = "Jack"
Jobs(11) = "Lucy"
Jobs(12) = "Bill"
Jobs(13) = "Larry"
Jobs(14) = "WIll"
Jobs(15) = "Deborah"
Jobs(16) = "Marie"
Jobs(17) = "Wayne"
Jobs(18) = "Denise"
Jobs(19) = "Dave"
Jobs(20) = "Judy"


For i = 0 To 6
ComboBox4.AddItem (Jobs(i))
Next i

For j = 7 To 12
ComboBox1.AddItem (Jobs(j))
Next j

For K = 13 To 18
ComboBox2.AddItem (Jobs(K))
Next K

For L = 19 To 20
ComboBox3.AddItem (Jobs(L))
Next L

End Sub

Private Sub CommandButton3_Click()
Dim i As Long, j As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng3a As Range
Dim rng4 As Range, rng4a As Range
Dim cbox As OLEObject


Set rng1 = Sheet3.Range("D91")
Set rng2 = Sheet1.Range("A98") ' "29 120"
Set rng3 = Sheet3.Range("B91")
Set rng3a = Sheet1.Range("C98:D98")
Set rng4 = Sheet3.Range("C91")
Set rng4a = Sheet1.Range("E98:F98")
For j = 1 To 4
Set cbox = Me.OLEObjects("ComboBox" & j)
For i = LBound(Jobs) To UBound(Jobs)
If Jobs(i) = cbox.Value Then
rng1.Offset(i, 0) = Jobs(i)
rng2.Value = "29 120"
rng3.Offset(i, 0).Value = Application.Sum(rng3a)
rng4.Offset(i, 0).Value = Application.Sum(rng4a)
End If
Next i, j


What this does is create a specified array with the names listed and
inserts them in the 4 comboboxes, then in the next field, depending on
the name chosen, it performs an arithmetic procedure and places that
value in another cell

I receive an error message of "type mismatch" and the line "For i =
LBound(Jobs) To UBound(Jobs)" is highlited

Could it be that it is because (Jobs) is only available to the first
field and not the second?

If so, how do I correct it??

Yes, the Job's array is only available from within CommandButton1's Click
event where is was declared. The solution is simple, just move it out side
of all the procedures, on a line by itself, at the beginning of the code.
You can still declare it as Private and all of the procedures on the form
will then be able to see it.

I'm guessing you know this (as you handled it correctly in your other
procedures), but figured I would mention it anyway. In this following line

Dim i, j, K, L As Integer

from your CommandButton1's Click event, only the L variable is declared as
an Integer... the rest will be declared as Variants. For those who this is
new to, VB and VBA requires each variable to be declared individually as to
its data type.

Rick


.

Quantcast