none
EXCEL - How to save TextBox value (placed on UserForm) to a cell on a sheet

    Question

  • Hello Friends,

    I have UserForm with few controls on it. I have i.e. a TextBox and when I type text in it and use a CommandButton to hide it and then again a CommandButton on the sheet to activate it again the text I typed in the TextBox has dissapeared.

    How the control values can be stored?

    It is probably very easy but I could't find an answere around here.
    Marcin
    Friday, August 28, 2009 11:46 AM

Answers

  • Hi, below will store the value in textBox1 in A1 on the current sheet. Replace ActiveSheet with the relevant Worksheet.

    ActiveSheet.Range("A1").Value = TextBox1.Value
    Friday, August 28, 2009 1:31 PM
  • OK I got it!

    'load textboxes values from a cell
    TextBox1.Value = Sheet1.Range("A1")


    'save textbox value to a cell
    Sheet1.Range("A1").Value = TextBox1.Value


    Nothing better than a little check-and-try on your own :-)

    Thank you!
    Marcin
    Friday, August 28, 2009 2:35 PM

All replies

  • Hi, below will store the value in textBox1 in A1 on the current sheet. Replace ActiveSheet with the relevant Worksheet.

    ActiveSheet.Range("A1").Value = TextBox1.Value
    Friday, August 28, 2009 1:31 PM
  • Hi ADG

    Thank you for your reply.

    All works perfect as long as I don't shut the UserForm with the close button (top-right corner cross).
    If I shut the Userform with command button the values are still there.

    What do I do wrong?
    Marcin
    Friday, August 28, 2009 2:28 PM
  • OK I got it!

    'load textboxes values from a cell
    TextBox1.Value = Sheet1.Range("A1")


    'save textbox value to a cell
    Sheet1.Range("A1").Value = TextBox1.Value


    Nothing better than a little check-and-try on your own :-)

    Thank you!
    Marcin
    Friday, August 28, 2009 2:35 PM
  • Try adding code to the Form close event to catch this

    Regards

    ADG
    Friday, August 28, 2009 4:37 PM
  • Hi Martin,

    Still going strong i see.  i am sure you are starting to see just what will be possible for you to do as you progress.  you will start feeling like you have more control over everything. 

    just some thoughts that may help you here.

    you have been in the mindset to use cells for storing data.  and really that is not where data is stored.  it is and it isn't.  say you wanted to store a value entered in a textbox to recall it the next time you open the form.  your first thought might be to store it in a cell value.  instead you can use a variable which will store it no matter what you do with the form.  now this would need to be a global public variable in a module though cause if the form is unloaded the variable will be lost if the variable is declared in the form code.  how and where variables are declared is known as scope.  and there's nothing wrong with storing values in cell values.  but sometimes you need to worry about whether or not you have that cell available.   in other words it is a little more manual and sometimes more complicated.

    but for your particular situation something you may not have considered is that typically when you use a form you would have a commit button or ok button, etc...  and you would have a cancel button or close.  these mean different things to different people.  but generally ok or commit means that you are accepting the changes that you made.  x to many might mean close but it can also mean cancel.  so typically the x button would be to cancel and not commit any changes.  and that is a matter of your preference, you just might want to keep in mind that it may mean different things to different people and you may atleast warn them with a message to be sure they understand they are committing changes. 

    this may be things you have thought about already.  just thought i would mention them to you in case you have not thought about this.

    also keep in mind that text is a property of many controls.  you can get the value and set the value of the text property.  many properties are like this where you can get the value and also set the value.  those are usually very directly worded in your code.
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    Saturday, August 29, 2009 4:55 PM