Popup Input Field When Opening Excel File

[mjonesjr84]

I have the code below to open the user input fields when the sheet first opens and it works fine.

Sub Auto_Open()

    ProjectInformation.Show


End Sub

My Project Information input field has info like date, quote number, location, room dimensions, system etc. It has 2 buttons on the first pop up form field that either closes the form field or moves to the next form field. On the 2nd form field there is 2 buttons to go back or to submit. The submit button runs the Save-As command to save the workbook.

My issue is that if I open the workbook again, it comes up blank. If I hit close button I put on the form it will keep all the original data that was input originally. If I hit submit to get the box to go away it will delete all the data that was input. I also have a button on the workbook that will open the input fields in the event I wanted to add or change info in the fields after the original info was put in. If I use this button, it will open with a blank input form.

I want the form fields to not open when opening the workbook after the workbook has been Saved-As. Does anyone have any ideas? I could email the file if that would help.

 

[My Aswer Is This]

Are we dealing with a UserForm?

And when you say input field
Do you mean Textbox ?

And what is a:

form field

I have been using Excel a long time and do not recognize these terms.
​

 

[mjonesjr84]

Are we dealing with a UserForm?

And when you say input field
Do you mean Textbox ?

And what is a:

form field

I have been using Excel a long time and do not recognize these terms.
​

See the image below. This is what I'm talking about.

 

[My Aswer Is This]

Thanks for that image. It appears to be a User Form.

Maybe someone else here on the forum will be able to help you.
I'm still not sure how to help you.

 

[Kenneth Hobson]

We would need to see all of the Userform's code I suspect.

If it was blank and you used SaveAs, the original file is still "blank".

If you save Userform control values to the worksheet, you can check for that in that event and skip the Show line. e.g.

Sub Auto_Open()
  If Worksheets(1).Range("A2").value="" Then ProjectInformation.Show
End Sub

 

[mjonesjr84]

Thanks for that image. It appears to be a User Form.

Maybe someone else here on the forum will be able to help you.
I'm still not sure how to help you.

Thanks. I'm not too well versed in proper Excel terms.

 

[mjonesjr84]

We would need to see all of the Userform's code I suspect.

If it was blank and you used SaveAs, the original file is still "blank".

If you save Userform control values to the worksheet, you can check for that in that event and skip the Show line. e.g.

Sub Auto_Open()
  If Worksheets(1).Range("A2").value="" Then ProjectInformation.Show
End Sub

How would I get the User Form code? When I open it in the Macro Editor, it is just the User Form.

When I put your code in I get "Run Time Error 13: Type Missmatch"

 

[mjonesjr84]

We would need to see all of the Userform's code I suspect.

If it was blank and you used SaveAs, the original file is still "blank".

If you save Userform control values to the worksheet, you can check for that in that event and skip the Show line. e.g.

Sub Auto_Open()
  If Worksheets(1).Range("A2").value="" Then ProjectInformation.Show
End Sub

I put it in exactly as you did and it still opens after data was input. If I modify the "A2" to a cell that would have input data in it from the User Form, I get the Error Code above.

 

[My Aswer Is This]

So can we assume you did not write the code?

If you built the User Form and all the textbox's or fields as you call them or they may be comboboxes or several other type controls you should know where the code is if you wrote the code.

 

[mjonesjr84]

So can we assume you did not write the code?

If you built the User Form and all the textbox's or fields as you call them or they may be comboboxes or several other type controls you should know where the code is if you wrote the code.

I did fumble through writing it. I know where the code is, but the Macro edit area for the User Form is a not the "back end" code. It creates the form in real time.

I did find the code for the forms.

Private Sub BTNExit_Click()

Workbooks("Clean Agent Proposal Tool (9-27-18)").Close SaveChanges:=True


End Sub


Private Sub BTNClose_Click()


    ProjectInformation.Hide
    
End Sub


Private Sub BTNNext_Click()
    
    ProjectInfo.InserProject_Info
    
    Sheet1.Unprotect
    Sheet1.Range("J2:L2").Value = ProjectInformation.TXTDate
    Sheet1.Range("J4:L4").Value = ProjectInformation.TXTQuoteNumber
    Sheet1.Range("A11:F11").Value = ProjectInformation.TXTProjectReference
    Sheet1.Range("A9:F9").Value = ProjectInformation.TXTProjectContact
    Sheet1.Range("G9:L9").Value = ProjectInformation.TXTCompany
    Sheet1.Range("G11:L11").Value = ProjectInformation.TXTProjectLocation
    Sheet1.Protect
    
    Sheet6.Unprotect
    Sheet6.Range("B6").Value = ProjectInformation.TXTSalesTaxRate
    Sheet6.Protect
    
    ProjectInformation.Hide
    
    ProtectedAreaInfo.Show
    
End Sub


Private Sub TXTSalesTaxRate_Change()


End Sub