Results 1 to 6 of 6

Setting a userform textbox default to a value in a specific cell on specific sheet?

This is a discussion on Setting a userform textbox default to a value in a specific cell on specific sheet? within the Excel Questions forums, part of the Question Forums category; I have a userform named "BID_Information". This userform is used to input project name, drawing number, area and description to ...

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    21

    Default Setting a userform textbox default to a value in a specific cell on specific sheet?

    I have a userform named "BID_Information". This userform is used to input project name, drawing number, area and description to multiple sheets so it populates certain cells which then flow through a formula and produce bid information. I have all of this working great, but it becomes a nuisance to have to type all of that back in each time you start a different area or drawing.

    Is there a way to populate this userform information to a hidden worksheet and then have the userform recall those cells when initiated? Kind of like a memory default from the last job that was bid.

    This way only one or two text boxes would need to be changed.

    I have a template set up so that I can open it, "save as" to a project file and begin the bid process, so I would imagine in order to keep the last one in memory, I would have to run a save command at the end of the submit button push, so it keeps revising the template file. I am sorry if I am not clear. I am very new to working with vba and not very talented at it.

    Any help will be appreciated.

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,614

    Default Re: Setting a userform textbox default to a value in a specific cell on specific sheet?

    Hi,
    try this & see if does what you want


    In a STANDARD module

    Code:
    Sub FormValues(ByVal Form As Object, ByVal StoreAction As Integer)
        Dim Ctl As Control
        Dim CtlType As String, UsersName As String
        Dim ApplicationName As String
        Dim Default As Variant
        
        UsersName = Environ("USERNAME")
        ApplicationName = Left$(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)
        
        For Each Ctl In Form.Controls
            CtlType = TypeName(Ctl)
            
            Select Case CtlType
                
            Case Is = "TextBox", "ComboBox", "OptionButton", _
                "CheckBox", "SpinButton"
                
                If StoreAction = xlClosed Then
                    
                    SaveSetting AppName:=ApplicationName, _
                    section:="UserForm Settings\" & UsersName & "\" & Form.Name, _
                    key:=Ctl.Name, setting:=CStr(Ctl.Value)
                    
                Else
                    If CtlType = "OptionButton" Or CtlType = "CheckBox" Then Default = False Else Default = ""
                    
                    Ctl.Value = GetSetting(AppName:=ApplicationName, _
                    section:="UserForm Settings\" & UsersName & "\" & Form.Name, _
                    key:=Ctl.Name, Default:=Default)
                End If
            End Select
            Next Ctl
    End Sub

    in your forms CODE PAGE

    Code:
    Private Sub UserForm_Initialize()
        FormValues Me, xlOpen
    End Sub
    
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        FormValues Me, xlClosed
    End Sub
    If any of the above events already exist in your userform code page you only need to copy the lines shown in red.

    If you have other forms in your project that you require to retrieve the last stored values, just include the event codes shown.


    Hope Helpful

    Dave

  3. #3
    New Member
    Join Date
    Aug 2015
    Posts
    21

    Default Re: Setting a userform textbox default to a value in a specific cell on specific sheet?

    Thanks for the reply Dave! I tried doing what you said and I must have things messed up. ugh. I started a new module, pasted what you said to paste. I think where I am pasting the form code is what is not working?

    Code:
    Option Explicit
    Private Sub BID_Information_Initialize()
    
    
        FormValues Me, xlOpen
    
    
    End Sub
    
    
    Private Sub image2_Click()
    
    
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim ws3 As Worksheet
        Dim ws4 As Worksheet
        Dim ws5 As Worksheet
        Dim ws6 As Worksheet
        Dim ws7 As Worksheet
        Dim ws8 As Worksheet
        Dim ws9 As Worksheet
        Dim ws10 As Worksheet
        
        Set ws1 = Worksheets("BID FORM")
        Set ws2 = Worksheets("Straight Duct")
        Set ws3 = Worksheets("Radius Elbow")
        Set ws4 = Worksheets("Rectangular Elbow")
        Set ws5 = Worksheets("TEE")
        Set ws6 = Worksheets("Transition")
        Set ws7 = Worksheets("Sq-Rnd")
        Set ws8 = Worksheets("End Cap")
        Set ws9 = Worksheets("Round Elbow")
        Set ws10 = Worksheets("AutoFillBidInfo")
        
            ws1.Range("C7") = ProjectTB.Value
            ws1.Range("Z1") = DrawingNoTB.Value
            ws1.Range("Z3") = AreaTB.Value
            ws1.Range("Y7") = DescriptionTB.Value
            ws1.Range("V5") = NameTB.Value
            ws1.Range("U8") = laggap.Value
            ws1.Range("V8") = girth.Value
            
            ws2.Range("AI23") = PCOST.Value
            ws2.Range("AI24") = LCOST.Value
            ws2.Range("AI25") = ACOST.Value
            ws2.Range("C9") = pm.Value
            ws2.Range("E9") = pga.Value
            ws2.Range("C12") = lm.Value
            ws2.Range("E12") = lga.Value
            ws2.Range("I13") = FrameAngleSize.Value
            ws2.Range("G13") = laggap.Value
            ws2.Range("G6") = girth.Value
            ws2.Range("C16") = INSTHICK.Value
            ws2.Range("C18") = INSDouble.Value
            ws2.Range("AI29") = ICost.Value
            ws2.Range("AH35") = GReq.Value
            
            ws3.Range("AI23") = PCOST.Value
            ws3.Range("AI24") = LCOST.Value
            ws3.Range("AI25") = ACOST.Value
            ws3.Range("C9") = pm.Value
            ws3.Range("E9") = pga.Value
            ws3.Range("C12") = lm.Value
            ws3.Range("E12") = lga.Value
            ws3.Range("I13") = FrameAngleSize.Value
            ws3.Range("G13") = laggap.Value
            
            ws4.Range("AI23") = PCOST.Value
            ws4.Range("AI24") = LCOST.Value
            ws4.Range("AI25") = ACOST.Value
            ws4.Range("C9") = pm.Value
            ws4.Range("E9") = pga.Value
            ws4.Range("C12") = lm.Value
            ws4.Range("E12") = lga.Value
            ws4.Range("I13") = FrameAngleSize.Value
            ws4.Range("G13") = laggap.Value
            
            ws5.Range("AI23") = PCOST.Value
            ws5.Range("AI24") = LCOST.Value
            ws5.Range("AI25") = ACOST.Value
            ws5.Range("C12") = pm.Value
            ws5.Range("E12") = pga.Value
            ws5.Range("C15") = lm.Value
            ws5.Range("E15") = lga.Value
            ws5.Range("G16") = FrameAngleSize.Value
            
            ws6.Range("AI23") = PCOST.Value
            ws6.Range("AI24") = LCOST.Value
            ws6.Range("AI25") = ACOST.Value
            ws6.Range("C9") = pm.Value
            ws6.Range("E9") = pga.Value
            ws6.Range("C12") = lm.Value
            ws6.Range("E12") = lga.Value
            ws6.Range("G16") = FrameAngleSize.Value
            
            ws7.Range("AI23") = PCOST.Value
            ws7.Range("AI24") = LCOST.Value
            ws7.Range("AI25") = ACOST.Value
            
            ws8.Range("AI23") = PCOST.Value
            ws8.Range("AI24") = LCOST.Value
            ws8.Range("AI25") = ACOST.Value
            
            ws9.Range("AI23") = PCOST.Value
            ws9.Range("AI24") = LCOST.Value
            ws9.Range("AI25") = ACOST.Value
            
            ws10.Range("A1") = ProjectTB.Value
            ws10.Range("A2") = DrawingNoTB.Value
            ws10.Range("A3") = AreaTB.Value
            ws10.Range("A4") = DescriptionTB.Value
            ws10.Range("A5") = NameTB.Value
    
    
    
    
    
    
    
    
            Unload Me
    End Sub
    
    
        
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        FormValues Me, xlClosed
    
    
    End Sub
    any idea where i might be going wrong?

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,614

    Default Re: Setting a userform textbox default to a value in a specific cell on specific sheet?

    You have renamed the UserForm_Initialize event with your userforms name.

    YOU MUST NOT RENAME THE EVENT CODE.

    It MUST look like this:

    Code:
    Private Sub UserForm_Initialize()
        FormValues Me, xlOpen
    End Sub
    Dave

  5. #5
    New Member
    Join Date
    Aug 2015
    Posts
    21

    Default Re: Setting a userform textbox default to a value in a specific cell on specific sheet?

    Winner Winner Chicken Dinner! That was exactly what I wanted! now all i have to do is try to understand what it all means. lol. Thanks Dave! I appreciate your patience with me. I am such a newbie at macros, etc. but I have the desire to learn, which is all that matters. Thanks again.

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,614

    Default Re: Setting a userform textbox default to a value in a specific cell on specific sheet?

    Quote Originally Posted by JeremyA1976 View Post
    Winner Winner Chicken Dinner! That was exactly what I wanted! now all i have to do is try to understand what it all means. lol. Thanks Dave! I appreciate your patience with me. I am such a newbie at macros, etc. but I have the desire to learn, which is all that matters. Thanks again.
    You may find this article:Sequence of events in Userforms • Pixcels.nl

    helpful regarding userform events.

    Glad solution worked for you.

    Dave

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com