Solved: passing data from a list box to a spreadsheet

[donavan]

I am trying to automate some processes here where I work and in doing so I am trying to build GUI front end for a spreadsheet database. I am using excel because my company is to cheap to buy Access or VB. I have some xperience in VBA but not a lot. That said here is the problem I am using a userform with a listbox. The list is populated from a dynamic list on the 'constants' sheet. I have managed to get the list to populate but I have been searching and not found how to pull a value from the list, once something is selected. Ideally I would like to put an index # (like #1 from the list of #7) but if I can only pull the value then I can work the code out. Any help would be great. Thanks ahead of time.

[anisurrahman]

I am trying to automate some processes here where I work and in doing so I am trying to build GUI front end for a spreadsheet database. I am using excel because my company is to cheap to buy Access or VB. I have some xperience in VBA but not a lot. That said here is the problem I am using a userform with a listbox. The list is populated from a dynamic list on the 'constants' sheet. I have managed to get the list to populate but I have been searching and not found how to pull a value from the list, once something is selected. Ideally I would like to put an index # (like #1 from the list of #7) but if I can only pull the value then I can work the code out. Any help would be great. Thanks ahead of time.



Private Sub submitButton_Click()
Dim ws As Worksheet

Set ws = Worksheets("index")
'change the worksheet name here


ws.Cells(1, 1).Value = listBoxTest.ListBox1.Value
'input ListBox1 value into worksheet named index or change it as per your worksheet name

End Sub



Private Sub UserForm_Initialize()

With Worksheets("constants")

ListBox1.RowSource = _
"'" & .Name & "'!" & .Range("A1", .Range("A65536").End(xlUp)).Address

End With
End Sub

[donavan]

First thanks for the reply. However this isn't quite what I am trying to pull off. It seems that your code is setup to dump the listbox selection directly to a cell. Though I could make this work, it would make things more complicated. What I would like, is to pass the value to a variable.

Here is what I have

Private Sub btn_Location_Click()
'******THIS SUB WORKS FINE********
'Loads Data (constants sheet)
Dim count As Integer
'Pulls count of list from "constants"
count = Worksheets("Constants").Cells(1, 1).Value
lsb_current.RowSource = "Constants!a3: a" & count + 2
frm_add.Caption = "Add a Location"
End Sub


Private Sub btn_remove_Click()
'********THIS SUB DOESN'T WORK********
' Tried setting remove_select as INT and STR
Dim remove_select As Integer
remove_select = listBoxTest.lsb_current.Value
End Sub



The remove sub obviously isnt complete. It errors out on the second line. I have tried it several different ways and it just doesn't seem to want to pass the value to the variable. I always get "0" when the VAR is set to INT and "" when the VAR is set to STR. Which leads to believe INT is the way to go.
Ideas?

[PIERO SOVICO]

My suggestion:
Update the Userform containing the ListBox
Add a TextBox on the UserForm
Add a CommandButton on the UserForm
CommandButton click event:
sub CommandButto... click()
TextBox...=Range(".....") the cell of the ControlSource of the ListBox
end sub
You see in the TextBox what you have selected.
Now choose where the TextBox contents have to bi written.
Suppose in Range("A30") of Sheets("XX") then the above CommandButton instruction have to be update as follows:

Sub CommandButto... click()
TextBox...=Range(".....") 'the cell of the ControlSource of the ListBox
Sheets("XXX").range("A30")=Textbox...
end sub

What you see in the textbox is written in Range A30, Sheet("XXX").
Hope this advise be usefull.
Piero

[PIERO SOVICO]

In this case you set a variable:
Dim ABC
and you modify the instruction
ABC = Textbox....
then you manages the variable as you wish.
Piero

[PIERO SOVICO]

To modify the Variable contents from "0" to 0 I use a simple solution that is
ABC = Textbox...
ABC = ABC * 1
Look into the variable. First you see "0" then you see 0

[anisurrahman]

First thanks for the reply. However this isn't quite what I am trying to pull off. It seems that your code is setup to dump the listbox selection directly to a cell. Though I could make this work, it would make things more complicated. What I would like, is to pass the value to a variable.

Here is what I have

Private Sub btn_Location_Click()
'******THIS SUB WORKS FINE********
'Loads Data (constants sheet)
Dim count As Integer
'Pulls count of list from "constants"
count = Worksheets("Constants").Cells(1, 1).Value
lsb_current.RowSource = "Constants!a3: a" & count + 2
frm_add.Caption = "Add a Location"
End Sub


Private Sub btn_remove_Click()
'********THIS SUB DOESN'T WORK********
' Tried setting remove_select as INT and STR
Dim remove_select As Integer
remove_select = listBoxTest.lsb_current.Value
End Sub



The remove sub obviously isnt complete. It errors out on the second line. I have tried it several different ways and it just doesn't seem to want to pass the value to the variable. I always get "0" when the VAR is set to INT and "" when the VAR is set to STR. Which leads to believe INT is the way to go.
Ideas?

please upload your file so I can have a look

[donavan]

I have uploaded a stripped down version of my file. When you open it it will only display the userform and not excel to get excel to return click the "make Excel visible" button on the last tab. Also when using the controls on the last tab it is setup to hide the list box and what not until you click one of the 3 buttons on the left side. After that the form should be fairly self-explanitory.

Really all I am looking for is one line of code just something like:

variable = listbox.value

(I know this isn't correct but its the closest I could think of)

[anisurrahman]

I have uploaded a stripped down version of my file. When you open it it will only display the userform and not excel to get excel to return click the "make Excel visible" button on the last tab. Also when using the controls on the last tab it is setup to hide the list box and what not until you click one of the 3 buttons on the left side. After that the form should be fairly self-explanitory.

Really all I am looking for is one line of code just something like:

variable = listbox.value

(I know this isn't correct but its the closest I could think of)


what's the password mate - your code is protected !!!!! :(

[anisurrahman]

I have uploaded a stripped down version of my file. When you open it it will only display the userform and not excel to get excel to return click the "make Excel visible" button on the last tab. Also when using the controls on the last tab it is setup to hide the list box and what not until you click one of the 3 buttons on the left side. After that the form should be fairly self-explanitory.

Really all I am looking for is one line of code just something like:

variable = listbox.value

(I know this isn't correct but its the closest I could think of)


.. Hope it will work :think:

Private Sub submitButton_Click()

Dim testData As String

testData = UserForm1.ListBox1.Value

msgbox testData

[donavan]

My bad ... the password is ... you ready for this ... "a"

Yeah I know kind of a pathetic attempt at security but it keeps the co-workers out of it and being that it isnt really a matter of security so much as a matter of keeping untrained hand out of it ... it works.


Also the post from anisurrhamn is exactly what I was needing. The part I was getting wrong was the =UserForm1.listbox.value part. I wasn't using the userform1 in front of the .listbox.value. Apparently that makes a huge difference. :)


Thanks everyone for all the help consider this solved

[anisurrahman]

My bad ... the password is ... you ready for this ... "a"

Yeah I know kind of a pathetic attempt at security but it keeps the co-workers out of it and being that it isnt really a matter of security so much as a matter of keeping untrained hand out of it ... it works.


Also the post from anisurrhamn is exactly what I was needing. The part I was getting wrong was the =UserForm1.listbox.value part. I wasn't using the userform1 in front of the .listbox.value. Apparently that makes a huge difference. :)


Thanks everyone for all the help consider this solved


SO FINALLY IT IS SOLVED.. HURRAY.. Please change the thread Title to SOLVED :D