CODEWITHSUNDEEP
excelvbauserform
GoldFusion
GoldFusion

Reputation: 149

Userform textboxes are blank after reopening excel file

Currently I have a userform with multiple page aka MultiPageand each of those MultiPagehas Textboxes which will take in inputs from user and each of those pages has a Back , Next button and specifically the next Button helps transferring the values from the Userform to the excel sheet called "DoNotPrint-Setup".

enter image description here

  Private Sub btnNext2_Click()


    If cbClient = "" Or tbProject.Value = "" Or tbNumber.Value = "" Or tbRevision.Value = "" Or tbDate.Value = "" Or tbPMOC.Value = "" Or tbPMOE.Value = "" Or tbClientE.Value = "" Or tbClientN.Value = "" Or tbClientP.Value = "" Or tbClientSA1.Value = "" Or tbClientSA2.Value = "" Then
    If MsgBox("Form is not complete. Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
    End If

End If

    With Sheets("DoNotPrint - Setup")
        .Range("C7").Value = Me.cbClient.Text
        .Range("C8").Value = Me.tbProject.Text
        .Range("C9").Value = Me.tbNumber.Text
        .Range("C10").Value = Me.tbRevision.Text
        .Range("C11").Value = Me.tbDate.Text
        .Range("C12").Value = Me.tbPMOC.Text
        .Range("C13").Value = Me.tbPMOE.Text
        .Range("C14").Value = Me.tbClientN.Text
        .Range("C15").Value = Me.tbClientE.Text
        .Range("C16").Value = Me.tbClientP.Text
        .Range("C17").Value = Me.tbClientSA1.Text
        .Range("C18").Value = Me.tbClientSA2.Text
End With
Me.MultiPage1.Value = 2

ending:
End Sub

This is what I have for Initialization process of the file below:

Private Sub UserForm_Initialize()

    WizardProp.MultiPage1.Value = 0
    Me.MultiPage1.Style = fmTabStyleNone

End Sub

And I have a Finish button at the end which will hide the Userform

Private Sub BtnFinish_Click()
    Me.Hide
End Sub

So right now it takes the output from the userform and inputs the values into the excel cell, but when I close the excel file the Userform textboxes are blank when its reopened. So is there a way to take the values in the excel cells and apply it to the Userform textboxes when Userform is reopened??

Upvotes: 0

Views: 168

Answers (1)

Cyril
Cyril

Reputation: 6829

You will need to create logic for

  • A) Determining if you are opening a fresh file (I recommend using a check to find the last row on your data sheet, as that's pretty simple... that is if you use a master file which contains no values)

  • B) Once you have checked A, then your initialize does one of two things (think If statement): 1) opens up blank, 2) opens up with values

Now the first part of B is pretty simple, you've already got that; the second part will just be inverting your With Statement:

With Sheets("DoNotPrint - Setup")
    .Range("C7").Value = Me.cbClient.Text
    .Range("C8").Value = Me.tbProject.Text
    .Range("C9").Value = Me.tbNumber.Text
    .Range("C10").Value = Me.tbRevision.Text
    .Range("C11").Value = Me.tbDate.Text
    .Range("C12").Value = Me.tbPMOC.Text
    .Range("C13").Value = Me.tbPMOE.Text
    .Range("C14").Value = Me.tbClientN.Text
    .Range("C15").Value = Me.tbClientE.Text
    .Range("C16").Value = Me.tbClientP.Text
    .Range("C17").Value = Me.tbClientSA1.Text
    .Range("C18").Value = Me.tbClientSA2.Text
End With

Becomes (when embedded in the Initiate routine):

With Sheets("DoNotPrint - Setup")
    Me.cbClient.Text = .Range("C7").Value
    '... 
    Me.tbClientSA1.Text = .Range("C17").Value
    Me.tbClientSA2.Text = .Range("C18").Value
End With

Upvotes: 2

Related Questions