refer to a userform in a formula

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?

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

"TimT" <[email protected]> skrev i melding
news:[email protected]...
> 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?

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.

"Harald Staff" wrote:

> 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
>
> "TimT" <[email protected]> skrev i melding
> news:[email protected]...
> > 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?
>
>
>

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

That has code that writes to a worksheet.

TimT wrote:
>
> 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?

--

Dave Peterson

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)

"Dave Peterson" wrote:

> Debra Dalgleish has a sample userform at:
> http://www.contextures.com/xlUserForm01.html
>
> That has code that writes to a worksheet.
>
> TimT wrote:
> >
> > 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?
>
> --
>
> Dave Peterson
>

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" wrote:

> Debra Dalgleish has a sample userform at:
> http://www.contextures.com/xlUserForm01.html
>
> That has code that writes to a worksheet.
>
> TimT wrote:
> >
> > 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?
>
> --
>
> 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.

TimT wrote:
>
> 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" wrote:
>
> > Debra Dalgleish has a sample userform at:
> > http://www.contextures.com/xlUserForm01.html
> >
> > That has code that writes to a worksheet.
> >
> > TimT wrote:
> > >
> > > 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?
> >
> > --
> >
> > Dave Peterson
> >

--

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 = ....





TimT wrote:
>
> 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)
>
> "Dave Peterson" wrote:
>
> > Debra Dalgleish has a sample userform at:
> > http://www.contextures.com/xlUserForm01.html
> >
> > That has code that writes to a worksheet.
> >
> > TimT wrote:
> > >
> > > 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?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson