none
Excel UserForm Text Boxes RRS feed

  • Question

  • I am relatively new at using VBA code. I have created a userform that is meant to treated as figure to display the results from a worksheet. I have been able to write coding that displays the results in the userform text box but if I change the results on the worksheet and then display the userform again the value in the textbox does not update unless I manually click in the textbox and hit enter. How do I get the text box in the userform when information in the sheet changes?
    Monday, December 24, 2018 9:25 PM

All replies

  • If you set the ControlSource Property of the TextBox to a cell on the worksheet then when the worksheet cell is changed then the TextBox will change also and vice versa if you change the value in the TextBox then the value on the worksheet will change.

    To set the TextBox properties, in Design Mode right click the TextBox and select Properties. Enter the worksheet name and cell address in the ControlSource property.

    Use syntax similar to the following example to link the worksheet name and cell address. If a cell address is entered without the worksheet name then the control links to the cell address of the worksheet that is active at the time of showing the Userform so best to include the worksheet name. Note the exclamation mark between the worksheet name and the cell address.

    Sheet1!A5


    Regards, OssieMac

    Thursday, December 27, 2018 7:10 AM
  • This answer would work if the text box was in an actual worksheet. The text box is in a picture that is being imported through visual basic coding. Thus, when the user clicks on a specific command button a pop up window appears showing the figure with built in text boxes. The text boxes are populated from information that is in a worksheet.
    Wednesday, January 30, 2019 5:10 PM
  • If you are using the Userform Initialize event to populate the controls on the Userform then it depends on how you close the Userform as to whether the controls are re-populated when you next show it.

    If you use code to close the userform and you only Hide it then the Initialize event is not called again next time you show it. If you are doing this then use the UserForm_Activate event to populate the controls. (Don't duplicate the code in both Initiaize and Activate evets or it runs in both events when you first show the userform.)

    If you use the code Unload Me (or Unload Userform1) to close the Userform then the Initialize event is called next time you show. Also if you use the cross top right of the userform to close then it is Unloaded.

    If this does not answer your question then can you upload an example to OneDrive and I will have a look at it for you.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and Ctrl and A should highlight the entire link and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Wednesday, January 30, 2019 7:57 PM