Array Questions

[DWTSG]

I would like to be able to click a button and display the next element in an
array. Currently I have a loop that just cycles through the elements of the
array on a single click of the button, here is my code
Sub Array_Owners()


Dim arrOwners As Variant
Dim strCurrOwner As String
Dim intX As Integer
Dim intY As Integer
Dim intCtr As Integer
intX = 1
intY = 1



arrOwners = Sheet1.Range("S3:S12").Value




For intCtr = 1 To 10
'MsgBox "On the Clock: " & arrOwners(intY, intX)
Sheet1.Cells(1, 7) = arrOwners(intY, intX)
intY = intY + 1
Next intCtr





End Sub

thanks in advance.

 

[Bob Kilmer]

DWTSG,
This was originally posted on 7/17 in response to a somewhat similar
request. It uses a Collection instead of an array. It was written to respond
to a double click on a cell but could be adapted easily to respond to a
button click. You might look up the original thread started 7/16 with
subject "Single command button to select one of several choices...".

Paste this code into a worksheet code module. It
will roll thru "Screws, Nails, Tacks" in B2 when you double-click on cell
B2. No controls required. The code contains all the necessary data. You can
specify any cell. You could put this code into a button event and change
Target to a particular cell or other object.

Option Explicit
Private mcolFasteners As Collection

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
'Changes cell B1 value among
'"Screws, Nails, Tacks" on double-click.

'Limit the effect to, oh, uh, cell B2.
If Target.Address <> Range("B2").Address Then
Cancel = False
Exit Sub
End If

On Error GoTo ErrHandler
Dim strBuf As String, v As Variant
'Instantiate and populate a collection
'if not already done.
If mcolFasteners Is Nothing Then
Set mcolFasteners = New Collection
With mcolFasteners
.Add "Screws", "Tacks"
.Add "Nails", "Screws"
.Add "Tacks", "Nails"
End With
End If

'Get, then clear the current value.
strBuf = LCase(Trim(Target.Text))
Target.Clear

'If it is in our collection, use it as
'an index to the next item in the collection.
For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Exit For
End If
Next v

'If not, just assign the cell one of the values.
If Len(Target.Value) < 1 Then
Target.Value = mcolFasteners(1)
End If

ErrHandler:
Cancel = True

End Sub

Bob Kilmer

 

[Bob Kilmer]

Adding this will make the combo hide right away upon choosing a value.

Private Sub ComboBox1_Click()
Me.ComboBox1.Visible = False
End Sub

 

[DWTSG]

Thanks a bunch.

Adding this will make the combo hide right away upon choosing a value.

Private Sub ComboBox1_Click()
Me.ComboBox1.Visible = False
End Sub


to