Each row is a different date. On Column G for each row, I want to have a drop down list that is based on what the user inputs each day. (The Input values has to be stored somewhere in the sheet/workbook as well.) There is no set # of items (but the Max is probably ~10 items).
I'm really new to Excel Programming, so I'm not sure if there is an easier way, but this is how I started doing it (with code below this description):
* So when the user selects Column G, Macro1 starts
* It asks for how many items to enter (Let's use 5 items as an example)
* Then it loops through that and gets Input for each item.
* The input values are stored across the selected row (i.e. if the user selects Row 3 of Column G and has 5 values to enter, I store their input in AA3:AE3
* Next I wanted to use that range (AA3:AE3 ) as the validation list range
* Since that range is a variable based on which row is selected, I set the validation formula to:
Formula1:=Range("AA" & (ActiveCell.Row) & ":AJ" & (ActiveCell.Row))
...and this is where I run into trouble. (Note I use AA3:AJ3 because there is a possibility of 10 items-if nothing is entered, it's just blank)
This one works though: Formula1:="=AA3:AJ3"
But I want it to be a variable on what row the user selects.
This one doesn't work: Formula1:="=Range("AA" & (ActiveCell.Row) & ":AJ" & (ActiveCell.Row))"
Code below
==================================================
When I run it, it gives me an error for the part in red:
Public Sub Macro1()
Dim num1 As Integer
num1 = InputBox("Enter # of items")
num1 = num1 - 1
Dim test(9) As String
For Index = 0 To 9
test(Index) = ""
Next Index
For Index = 0 To num1
test(Index) = InputBox("Enter item# " & Index)
Next Index
Range("AA" & (ActiveCell.Row)).Select
ActiveCell.Value = test(0)
Range("AB" & (ActiveCell.Row)).Select
ActiveCell.Value = test(1)
Range("AC" & (ActiveCell.Row)).Select
ActiveCell.Value = test(2)
Range("AD" & (ActiveCell.Row)).Select
ActiveCell.Value = test(3)
Range("AE" & (ActiveCell.Row)).Select
ActiveCell.Value = test(4)
' ...
ActiveCell.Value = test(9)
Range("G" & (ActiveCell.Row)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Range("AA" & (ActiveCell.Row) & ":AJ" & (ActiveCell.Row))
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
======================================================================================
Edit: Oh, and the reason I don't want the user to enter their list of items into the Data->Validation->List themselves (besides them not being familiar with it) is because:
Each 'item' is actually a sentence long (so the small box is hard to edit)
I actually need the items listed somewhere(anywhere) on the sheet as well to be able to write some functions
Bookmarks