thanasis380
New Member
- Joined
- Oct 6, 2017
- Messages
- 3
I have a range (Myrange) with three columns, the first two (eg Name, Address) are filled and the third (eg Age) will be filled through a form.
So I made a listbox with rowsource (Myrange) and the user will select his name and then an inputbox will ask for a numeric value.
The value is inserted in Myrange in the sheet and simultaneously the rowsource of the listbox is refreshed.
Unfortunately my code asks twice for the age. Any suggestions to solve that?
Thanks in advance.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">PrivateSub UserForm_Initialize()
With ListBox1
.ColumnCount =3
.ColumnHeads =True
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectSingle
.RowSource ="Sheet1!A2:C6"
.ListIndex =-1
EndWith
EndSub
PrivateSub ListBox1_Click()
Dim Myrange AsString
Myrange = ListBox1.RowSource
For i =0To ListBox1.ListCount -1
If ListBox1.Selected(i)=TrueThen
ExitFor
Else
EndIf
Next i
x = Application.InputBox("Insert age")
y = ListBox1.ListIndex
ListBox1.RowSource = vbNullString
Sheets("Sheet1").Cells(y +2,3).Value = x
ListBox1.RowSource = Myrange
EndSub</code>
So I made a listbox with rowsource (Myrange) and the user will select his name and then an inputbox will ask for a numeric value.
The value is inserted in Myrange in the sheet and simultaneously the rowsource of the listbox is refreshed.
Unfortunately my code asks twice for the age. Any suggestions to solve that?
Thanks in advance.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">PrivateSub UserForm_Initialize()
With ListBox1
.ColumnCount =3
.ColumnHeads =True
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectSingle
.RowSource ="Sheet1!A2:C6"
.ListIndex =-1
EndWith
EndSub
PrivateSub ListBox1_Click()
Dim Myrange AsString
Myrange = ListBox1.RowSource
For i =0To ListBox1.ListCount -1
If ListBox1.Selected(i)=TrueThen
ExitFor
Else
EndIf
Next i
x = Application.InputBox("Insert age")
y = ListBox1.ListIndex
ListBox1.RowSource = vbNullString
Sheets("Sheet1").Cells(y +2,3).Value = x
ListBox1.RowSource = Myrange
EndSub</code>