No announcement yet.

Problem writing from text boxes on user form to cells in Excel speadsheet

  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem writing from text boxes on user form to cells in Excel speadsheet

    Hi. This is my first post to this forum. I have a user form that has 6 text boxes. I know that the values are being assigned to variables correctly because I have them all listed in a message box (one below the other) after the user hits OK after entering the sixth value. However, when I then try to write those 6 values to a column in Excel, only the first value is written, and this is always the value in the first 'Activecell.value.....' statement in the routine. The problem is that I want all 6 values listed, in cells E1:E6. I am pasting the code below that is associated with the OK key at the bottom of the user form. I assume I am making a mistake here that will be obvious to some or most of you.

    Public Sub OK_Click()
    MsgBox Mycell & vbNewLine & _ 'displays values of variables from text boxes in user form
    Condition_name & vbNewLine & _
    MyNum & vbNewLine & _
    trial_start & vbNewLine & _
    Baseline_num & vbNewLine & _
    Range("E1").Value = UserForm1.User_inputs.Value 'should write input values to text boxes to cells in Excel
    Range("E2").Value = UserForm1.Sheet_name.Value
    Range("E3").Value = UserForm1.Trial_num.Value
    Range("E4").Value = UserForm1.Start_time.Value
    Range("E5").Value = UserForm1.Baseline_cell_num.Value
    Range("E6").Value = UserForm1.OSR_trials.Value
    End Sub
    Last edited by StephenR; February 6th, 2019, 22:04. Reason: add code tags

  • #2
    Welcome to Ozgrid. Please remember to use code tags in future as per forum rules.

    Actually there's nothing obviously wrong with that code, particularly if the first one works. Possibly a silly question but is there definitely text in all the boxes? Could you have any sheet formatting that might affect things - are the values definitely not there as opposed to just being not visible? Also check the textbox names are definitely correct.


    • #3
      Thank you for your response Stephen.
      I just tried the code again, but instead of trying write the data from the user form text boxes to the Excel cells, I wrote the values stored in the variable names. Doing that, everything was written to the Excel sheet correctly. I know that when I tried it before, the data were entered into the user form, but is it possible that the values somehow got erased upon hitting the OK button? I ask this because when I run that small amount of code, each time the user form appears it has the same values as from the first time I entered values, except for 1 cell that has no value at all (even though a value is entered each time I complete the form).
      I assume this won't be a problem when I use Load and Unload commands in the larger program this will be a part of.
      Finally, I saw the info on line about how to format code when posting to the forum, but honestly, I didn't understand what I was supposed to do. Is it as simple as enclosing the code snippet in certain characters (e.g., in LaTeX using ``` before and after the code)?


      • #4
        The values will not be retained between runs but shouldn't be erased while running the code. Can you upload a small workbook which illustrates the behaviour?

        Wrap the code using the # button on the editor or write [code] and closing tag [ /code] (without the space).


        • #5
          Thanks Stephen.
          I am happy to report that I got the code to work both ways: Writing the values stored in variable names to the spreadsheet, and writing the values in the text boxes to the spreadsheet.
          I noticed that in addition to the procedures (Subs) for the text boxes and the OK and Cancel buttons on the user form I created, there were some extra procedures that were empty. Once I deleted these, then the code worked in terms of writing the values in the text boxes to the spreadsheet. I have noticed that sometimes when I'm creating a user form, there are extra procedures listed in the code window (perhaps from controls I created and deleted, or edits I made? I'm not really sure). Anyway, thank you for your offer of assistance. I'm sure I will have questions in the future, but at least things are good for now.


          • #6

            Yes, that's a pain. It usually happens when you right click and View Code while the mouse arrow is inside a control. You can just delete them. Glad it all worked.