+ Reply to Thread
Results 1 to 8 of 8

UserForm - How to retain wrksht data in userform and reverse

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    UserForm - How to retain wrksht data in userform and reverse

    Hello everyone.
    I am new to UserForms and code, but getting a little handle on it. I am stuck right now with (probably) a simple problem though, as follows; I want the UserForm (UF) to show the data that is in the wrksht if there is data in the wrksht. If not, then the user can use the UF to input the data upon "save and exit" command button. In this way, the user will be able to see on the UF all the data he has entered.
    I have tried with this code for one textbox and one cell (don't laugh); Any assistance would be greatly appreciated. Thanks, JasonRay

    Private Sub ExitFootingInputUserForm_Click()
    Unload a001UF_FootingInput
    Dim ws As Worksheet
    Set ws = Worksheets("Takeoff001")
    If ws.Range("E19").Value > 0 Then
    TextBox01.Value = ws.Range("E19").Value
    Else
    ws.Range("E19") = TextBox01.Value

    End If

    Unload a001UF_FootingInput

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: UserForm - How to retain wrksht data in userform and reverse

    I never laugh at people who have a go

    Try this code in your userform module

    Please Login or Register  to view this content.
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: UserForm - How to retain wrksht data in userform and reverse

    Mr. Litch, It worked! Thank you. .........although, I have 190 textboxes in the userform. Am I assuming correct when I assume the following code must be repeated (with edited textbox id# identified), 190 times?

    Private Sub UserForm_Initialize()
    UpDateFlag = False
    Me.TextBox01.Text = Range("E19").Value
    UpDateFlag = True
    End Sub
    Private Sub TextBox01_Change()
    If UpDateFlag = False Then Exit Sub
    Range("E19").Value = Me.TextBox01.Text
    End Sub

    If so, perhaps you may know of a global solution? If not - I am still very appreciative as you have provided something of great value!

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: UserForm - How to retain wrksht data in userform and reverse

    Yes you are correct.. You would need a sub for each textbox for real time updating and that would not be good for 190 textboxes..

    alternatively you could have an update button and use a looping routine to iterate

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: UserForm - How to retain wrksht data in userform and reverse

    I got the first one to work with 7 cells. It took long enough that I want to understand your second answer. If you find the time please let me know if or where and why I am wrong on the following synopsis of your second code write;
    Questions are bracketed []
    Sub UpDateSheet ()...............[I can use this as is because it is global - correct?]
    for x = 1 to 190.............[What does "x" signify? Do I need to change it to mean something specific to my userform and/or worksheet?]
    Sheet1.cells (X,1) .value = Me ("Textbox" & X) .Text..............[Should I change Sheet1.cells to my specific worksheet name?...... What does the "(X,1) signify?......Do I need to change ("Textbox"...to a specific textbox?]
    next x................[enter as is or does the "x" need to be something more specific to my userform and/or worksheet?]
    Andy - you would have the patience of Job to answer all the above. I appreciate your efforts thusfar.
    JasonRay

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: UserForm - How to retain wrksht data in userform and reverse

    Sub UpDateSheet ()...............[I can use this as is because it is global - correct?]
    Yes it will update all cells from all textboxes

    for x = 1 to 190.............[What does "x" signify?
    X will count from 1 to 190

    Do I need to change it to mean something specific to my userform and/or worksheet?]
    Worksheet yes ... Userform - Not if your textboxes have their default names - Textbox1 etc


    Sheet1.cells (X,1) .value = Me ("Textbox" & X) .Text..............[Should I change Sheet1.cells to my specific worksheet name?......
    Yes.. You will see the name in the VBE project explorer.. The given name is in brackets

    What does the "(X,1) signify?......Do I need to change ("Textbox"...to a specific textbox?]
    Cells(X,1) specifies row X, column 1........ In this case it writes the value of the textboxes sequentially into column A.. This is just notional for demo and will need to be amended to suit your spreadsheet.

    next x................[enter as is or does the "x" need to be something more specific to my userform and/or worksheet?]
    As is... It's a composite part of the For X loop and sends the computer back to the "For" line if X is less than the max value (192)

    You're more than welcome

  7. #7
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: UserForm - How to retain wrksht data in userform and reverse

    Ok then, this is what I did;
    When you first presented the 2nd code write you mentioned it needed to be controlled by an update button. I installed a command button (called “Refresh” on the userform in VBA Design mode), then copied and amended your code to it.
    Then I got out of Design mode, went to the wrksht, opened the UserForm, clicked the refresh button and none of the data in the first 7 cells appeared in the UserForm. Alternately, I input data in the first 7 textboxes of the UserForm, (numbered 1 through 7, I double-checked), and then hit refresh. None of the data moved to the wrksht.

    Code with descriptions follows then the code as written and installed.
    Any ideas why it doesn’t work?

    Private Sub CB006_Rfrsh_Click()
    [name of the “Refresh” Command Button]

    UpDateSheet()
    [Kept as is]

    For x = 1 To 7
    [Keeping it simple by trying only the first 7 textboxes and cells]

    Takeoff001.Cells(19, 5).Value = Me("Textbox" & x).Text
    [Takeoff001 is the name of the wrksht, 19 is the row number and 5 is the column number of the first cell/textbox data location].

    Next x
    [Kept as is]

    End Sub

    Private Sub CB006_Rfrsh_Click()
    UpDateSheet()
    For x = 1 To 7
    Takeoff001.Cells(19, 5).Value = Me("Textbox" & x).Text
    Next x

    End Sub

    Off-Topic - What does "Me" mean?
    Also - how does one get to the posting format on this forum so that he can alter the font? I see you did it and it makes it much easier to follow.

  8. #8
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: UserForm - How to retain wrksht data in userform and reverse

    ...........so close Andy. I appreciate all your efforts in assisting me to figure this thing out.
    I apologize for being such a noob and getting you in a little deep.

    If and when you ever get a chance please check it out for me.
    Thank you much.

    JasonRay

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1