Reporting data to UserForm

[todd]

I am trying to create a UserForm that will display data from a worksheet onto
the UserForm, while at the same time allowing input from the same UserForm
from a TextBox - what toolbox feature should I use, and how would I do this?

[Michael]

You can use the Text Box Control for both, and in the value properties point
to the cell reference where the data is currently stored; make sure you set
to true the locked properties. For the data going you do the same, you placed
a cell worksheet and cell reference in the value, and create an on enter
event that will submit the data in the text box to the cell reference
Also, you may want to change the background colors and the special effect to
flat for the data currently in the spreadsheet for distinction purposes.



"todd" wrote:

> I am trying to create a UserForm that will display data from a worksheet onto
> the UserForm, while at the same time allowing input from the same UserForm
> from a TextBox - what toolbox feature should I use, and how would I do this?

[todd]

Michael,

Thanks for all the help. I do have a few follow-up questions. What is the
appropriate syntax to display values back from a worksheet, and can they be
based on a counter value (e.g. - worksheets("Checkbook").cells(i,2).value,
where i is a counter)? Since excel uses some abbreviations that are not the
most intuitive, would you provide those when referring to UserForm TextBox
fields (like in your original reply)?

Todd

"Michael" wrote:

> You can use the Text Box Control for both, and in the value properties point
> to the cell reference where the data is currently stored; make sure you set
> to true the locked properties. For the data going you do the same, you placed
> a cell worksheet and cell reference in the value, and create an on enter
> event that will submit the data in the text box to the cell reference
> Also, you may want to change the background colors and the special effect to
> flat for the data currently in the spreadsheet for distinction purposes.
>
>
>
> "todd" wrote:
>
> > I am trying to create a UserForm that will display data from a worksheet onto
> > the UserForm, while at the same time allowing input from the same UserForm
> > from a TextBox - what toolbox feature should I use, and how would I do this?

[Michael]

When you insert the textbox control in your form you will see the properties
windows appearing on the bottom left of the visual basic editor. Click on the
"Categorized" tab, scroll down to where the data properties are, and type in
the "Control Source" the Worksheet Cell Reference (ie.: Sheet1!$A$1)
Then scroll back up to the appearance properties and change the
"SpecialEffect" from "2 - fmSpecialEffectSunken" to "0 - fmSpecialEffectFlat"
and "the BackColor" to a color that will better represent the data currently
in the Cell.
Then Insert a second text box and do exactly the same thing except for
changing the Appearance of the object so the user understand that is a field
where they can key in data. Now insert a "CommandButton" Control and label it
"Update Data"; behind this botton you insert the code that will send the data
to the spreadsheet on_click, for example: Sheets("Sheet1").Range("A1").Value
= Textbox2.value

I hope this is what you need,

Cheers,

"todd" wrote:

> I am trying to create a UserForm that will display data from a worksheet onto
> the UserForm, while at the same time allowing input from the same UserForm
> from a TextBox - what toolbox feature should I use, and how would I do this?

[todd]

How can I have the Worksheet Cell Reference be based on a counter? For
example, I have a macro that is running that calls this userform, and I want
the value reported to that textbox to be based on a counter location (like
Sheet1!$B<counter variable i>).

"Michael" wrote:

> When you insert the textbox control in your form you will see the properties
> windows appearing on the bottom left of the visual basic editor. Click on the
> "Categorized" tab, scroll down to where the data properties are, and type in
> the "Control Source" the Worksheet Cell Reference (ie.: Sheet1!$A$1)
> Then scroll back up to the appearance properties and change the
> "SpecialEffect" from "2 - fmSpecialEffectSunken" to "0 - fmSpecialEffectFlat"
> and "the BackColor" to a color that will better represent the data currently
> in the Cell.
> Then Insert a second text box and do exactly the same thing except for
> changing the Appearance of the object so the user understand that is a field
> where they can key in data. Now insert a "CommandButton" Control and label it
> "Update Data"; behind this botton you insert the code that will send the data
> to the spreadsheet on_click, for example: Sheets("Sheet1").Range("A1").Value
> = Textbox2.value
>
> I hope this is what you need,
>
> Cheers,
>
> "todd" wrote:
>
> > I am trying to create a UserForm that will display data from a worksheet onto
> > the UserForm, while at the same time allowing input from the same UserForm
> > from a TextBox - what toolbox feature should I use, and how would I do this?

[Michael]

Based on your previous example: worksheets("Checkbook").cells(i,2).value; all
you have to do is assign this statement to a variable.
Var1=worksheets("Checkbook").cells(i,2).value
And then call the variable into your text box
Textbox1.value = Var1
For example if your i= 3 then
In the text box you will see the same value you have in cell B3 in your
checbook sheet.
Cheers,
Michael


"todd" wrote:

> I am trying to create a UserForm that will display data from a worksheet onto
> the UserForm, while at the same time allowing input from the same UserForm
> from a TextBox - what toolbox feature should I use, and how would I do this?

[todd]

Is there some setting that I need to change, as I simply get the string I
type into the "Text" cell echoed back into the UserForm?

"Michael" wrote:

> Based on your previous example: worksheets("Checkbook").cells(i,2).value; all
> you have to do is assign this statement to a variable.
> Var1=worksheets("Checkbook").cells(i,2).value
> And then call the variable into your text box
> Textbox1.value = Var1
> For example if your i= 3 then
> In the text box you will see the same value you have in cell B3 in your
> checbook sheet.
> Cheers,
> Michael
>
>
> "todd" wrote:
>
> > I am trying to create a UserForm that will display data from a worksheet onto
> > the UserForm, while at the same time allowing input from the same UserForm
> > from a TextBox - what toolbox feature should I use, and how would I do this?

[Michael]

O.K. Do me a favor, describe your form, describe the data you have in the
spreadsheet, describe step by step the process you are following and if
possible paste the code you are running on this forum, so I can have a better
Idea of where the breakdown occurs. If you prefer, you can create dummy data
(For Privacy purposes) and email your workbook to me: [email protected].




"todd" wrote:

> Is there some setting that I need to change, as I simply get the string I
> type into the "Text" cell echoed back into the UserForm?
>
> "Michael" wrote:
>
> > Based on your previous example: worksheets("Checkbook").cells(i,2).value; all
> > you have to do is assign this statement to a variable.
> > Var1=worksheets("Checkbook").cells(i,2).value
> > And then call the variable into your text box
> > Textbox1.value = Var1
> > For example if your i= 3 then
> > In the text box you will see the same value you have in cell B3 in your
> > checbook sheet.
> > Cheers,
> > Michael
> >
> >
> > "todd" wrote:
> >
> > > I am trying to create a UserForm that will display data from a worksheet onto
> > > the UserForm, while at the same time allowing input from the same UserForm
> > > from a TextBox - what toolbox feature should I use, and how would I do this?

[ramesharma]

Hi thanks for solution