Array Questions
 

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.

Array Questions
 

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

"DWTSG" wrote in message
...
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.

Array Questions
 

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

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


"Bob Kilmer" wrote in message
...
Place a combo on a worksheet. On sheet activation, this code will populate
the combo with the contents of range H2:H12 (change to suit) and align the
combo with cell B2 (change to suit) and hide the combo. When you click on
cell B2, the combo becomes visible. Click a value, then click away. The
combo will hide again but leave the selected value in the cell. It sizes
the
combo to the row/column size. If the combo is too small, resize the
row/column slightly.

Option Explicit
Private rng As Range

Private Sub Worksheet_Activate()
'(change rng to suit)
Set rng = Me.Range("B2")
With Me.ComboBox1
.Visible = False
'(change fill range to suit)
.ListFillRange = "H2:H12"
'(change linked range to suit)
.LinkedCell = rng.Address
.Left = rng.Left
.Top = rng.Top
.Width = rng.Width
.Height = rng.Height
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = rng.Address Then
ComboBox1.Visible = True
Else
ComboBox1.Visible = False
End If
End Sub

Bob Kilmer
"DWTSG" wrote in message
...
This works great, except I am going to have 10 items to choose from so I
think it may be too much clicking to get to the end for the list. Is
there
anyway to click into a cell and have it give you a drop down choice or a
pop
up choice? Thanks for the help.
"Bob Kilmer" wrote in message
...
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

"DWTSG" wrote in message
...
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.

Array Questions
 

Thanks a bunch.
"Bob Kilmer" wrote in message
...
Adding this will make the combo hide right away upon choosing a value.

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


"Bob Kilmer" wrote in message
...
Place a combo on a worksheet. On sheet activation, this code will
populate
the combo with the contents of range H2:H12 (change to suit) and align
the
combo with cell B2 (change to suit) and hide the combo. When you click
on
cell B2, the combo becomes visible. Click a value, then click away. The
combo will hide again but leave the selected value in the cell. It sizes
the
combo to the row/column size. If the combo is too small, resize the
row/column slightly.

Option Explicit
Private rng As Range

Private Sub Worksheet_Activate()
'(change rng to suit)
Set rng = Me.Range("B2")
With Me.ComboBox1
.Visible = False
'(change fill range to suit)
.ListFillRange = "H2:H12"
'(change linked range to suit)
.LinkedCell = rng.Address
.Left = rng.Left
.Top = rng.Top
.Width = rng.Width
.Height = rng.Height
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = rng.Address Then
ComboBox1.Visible = True
Else
ComboBox1.Visible = False
End If
End Sub

Bob Kilmer
"DWTSG" wrote in message
...
This works great, except I am going to have 10 items to choose from so
I
think it may be too much clicking to get to the end for the list. Is
there
anyway to click into a cell and have it give you a drop down choice or
a
pop
up choice? Thanks for the help.
"Bob Kilmer" wrote in message
...
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

"DWTSG" wrote in message
...
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.