refer to a userform in a formula

[Guest]

I have a userform that collects information that needs to be referenced later
on in a procedure.
How would I reference the value of an option button or listbox, combobox
etc. in a formula/ if statement?
Any takers?

 

[Harald Staff]

A procedure is not a formula, so your question is quite confusing.
The common ways are to have a procedure running while, or started from, the
userform while it's there, or to have the form write information to a
worksheet for later use.

HTH. Best wishes Harald

 

[Guest]

Sorry about that, my terminology is bad.
How can I store the results of the userform to cells in the workbook (a
hidden vlookup sheet). The user is collecting information that will be used
in formulas and in code.

 

[Dave Peterson]

Debra Dalgleish has a sample userform at:
http://www.contextures.com/xlUserForm01.html

That has code that writes to a worksheet.

 

[Guest]

Thanks Dave,
if I wanted to refer to a specific cell that I named would the syntax be
ws.range ("rangename')
or
ws.cells ("rangename")
or
ws.cells (a:2)

 

[Guest]

I'm having a tough time with this one. I'm missing something in my syntax

Private Sub Start_Click()

Dim ws As Worksheet
Set ws = Worksheets("vlookup")

'copy the data to the database
ws.Cells(1, 3).Value = frm_Data.TaxType.Value

it's not returning the value into the cell.
Taxtype is a listbox, is there a property that could be set wrong?

 

[Dave Peterson]

What kind of listbox is TaxType?

Does it support selecting multiple options--or can you only select one?

Your snippet of code looks ok to me.

Maybe an extra line:

msgbox frm_data.taxtype.value

would help show you the problem.

 

[Dave Peterson]

If the cell had a name:
ws.range("myName").value = ....

if you knew the row and column number of the cell:
ws.cells(row#,col#).value = ....