Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
Array Functions - Two Questions | Excel Worksheet Functions |