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.


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

  • 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)?

  • 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).

  • 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.
    Steve

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!