Is it possible for a user form to inherit editable text from a worksheet?

[dbancroft]

Hello all,

I'm still working on my design problem: allowing (very non-technical) users to edit Excel data in a format that doesn't look like a spreadsheet. A User Form the same size as the Excel screen might work, but only if User Forms can pick up data from the source worksheet, allow users to edit that data, and save it back to the source worksheet. Is that possible? And how would I do that? (I am NOT a programmer, just a tech writer being pressed into service!)

Thank you!!

 

[Lewiy]

This is a very simple example, but if you create a UserForm with a TextBox and a CommandButton, then put this code behind it:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o:p></o:p>

Private Sub CommandButton1_Click()<o:p></o:p>
Range("A1") = TextBox1.Value<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
Private Sub UserForm_Activate()<o:p></o:p>
TextBox1.Value = Range("A1")<o:p></o:p>
End Sub

<o> </o>
When the UserForm is opened, the TextBox will take on the value of cell A1 on the active sheet. When the CommandButton is pressed, it will send the current contents of the TextBox back to cell A1.
<o> </o>
However, what you are doing sounds like quite a big task, so you are going to need to become very much more familiar with the basic VBA concepts to achieve this properly I would think.

 

[dbancroft]

Hey, that looks promising!

Is it possible to repeat that code so that multiple fields can appear in the same textbox? Oh... just looked again, no -- parsing it back out would be craziness (if not impossible).

So I need multiple textboxes, with this code in each (but edited to say Range("B1")=TextBox2.value, etc.)

I'll try it out and post a "Solved" message if it works.

Thanks very much,

Deb.

 

[Legacy 98055]

Hi Deb. You may also wish to look into the ControlSource property. This property links a range to a control - such as a textbox. If data is changed in the textbox, it is automatically reflected in the range - visa versa...

 

[dbancroft]

OK, really dumb question: I've created a user form but it doesn't open when I open the Excel file. I assume I have to tell it to open (duh) but not sure how.

 

[dbancroft]

And Tom -- does ControlSource do something different from Lewiy's code below? Or is it just a more straightforward way to do it?

 

[Lewiy]

You do indeed need to “tell” the UserForm to open. You can do this with the code:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o:p></o:p>

UserForm1.Show<o:p></o:p>

<o></o>
<o> </o>
You will need to decide what will trigger the form to open. You could put a CommandButton on a worksheet with that code behind it, or you could use an “Event” such as Workbook_Open() or Worksheet_Activate() to trigger the code.<o></o>
<o> </o>
The ControlSource property will link the TextBox directly with a particular cell or range in a worksheet. Depending on your actual intended use of the UserForm, this may or may not be the best option. It would be useful if you have a 1:1 relationship between the TextBox and a particular cell, i.e. the one TextBox will ALWAYS affect and be affected by the same cell.<o></o>

 

[dbancroft]

So:

Workbook_Open(UserForm1.Show)

?

And I assume that's on the Worksheet?


Another question: The Text Box on the User Form opens with existing code:
Private Sub TextBox1_Change()

End Sub

Do I want to keep that & insert my code between?

 

[Lewiy]

To use the Workbook_Open() Event, you need to place the code in the ThisWorkbook module (which can be opened by right clicking on the excel logo in the top left of Excel next to the file menu and clicking View Code). You would want something like this:

Private Sub Workbook_Open()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
UserForm1.Show<o:p></o:p>
End Sub

<o> </o>
Re the textbox:
Every object has a number of events associated with it that you can choose from. The code:

Private Sub TextBox1_Change()

End Sub<o:p></o:p>

<o></o>
<o> </o>
Is just the default event. If you wanted something to happen when the textbox value changes then you would write the code between these lines. If not then you can leave it there as it is, or delete it.<o></o>
<o> </o>
You will notice two drop down menus at the top of the VB Editor. The left one will allow you to select an object (i.e. UserForm, TextBox, CommandButton, etc.) and the right one allows you to select the event that you want to use based on the selected object. Once you select an event, it will place the first and last lines (similar to what you had for the text box) into the module. All code written that you want to be invoked by that event, would be written between these two lines.