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

JeremyA1976

Board Regular
Joined
Aug 3, 2015
Messages
59
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi,
try this & see if does what you want


In a STANDARD module

Rich (BB 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

Rich (BB 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
 
Upvote 0
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?
 
Upvote 0
You have renamed the UserForm_Initialize event with your userforms name.

YOU MUST NOT RENAME THE EVENT CODE.

It MUST look like this:

Rich (BB code):
Private Sub UserForm_Initialize()
    FormValues Me, xlOpen
End Sub

Dave
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top