VBA TextBox Upon Opening, Adding Userform, Not Prompted After Initial Save

[jcc88]

I have a template for my team to use, that is saved on a shared drive. Currently, I have this code:

Private Sub workbook_open()


MsgBox "Please save this file and rename to the appropriate folder."

MsgBox "Don't forget to pick the appropriate Sales Person!"


End Sub


What I ACTUALLY want it to do, is for a userform (?) to pop up, prompting them to rename the file and save in a place of their own choosing.

The second thing is, once they have done the above, there will be another prompt for them to choose their name from a drop down list that will auto populate their name and details (email, address, phone, title) into 1 cell on the cover page.

The above should only prompt them once. Once it is saved,re-named and the applicable person is chosen, this should not open up again anytime they go into this workbook.

I've looked at many resources online and have found bits of what I need, but nothing that allows me to meld them all together.

[paulked]

Hi and welcome to the forum

something like this? VBAX28a.xlsm

[jcc88]

Yes! That's exactly what I want. I saw your code

Private Sub Workbook_Open()
If shHiddenData.Cells(1, 1) = 1 Then Exit Sub
frmGetName.Show
End Sub

But how do I implement it in my book?

[paulked]

Add a new sheet to your workbook and rename it shHiddenData.

Put your list of names in column A (from row 2 downwards, keep row 1 clear or you will lose that name!)

Copy the Workbook_Open() code to ThisWorkbook code module in your workbook.

Hide the sheet.

You can design your own form or just export frmGetName from the file above and import it to your workbook.

Hope that helps.

[jcc88]

Thanks, I want to add a code in there to also cancel the userform as if we go into the template to fix things, that box will always pop up with no way to override it without having to save it? Almost like a "Cancel" button

[paulked]

add a button and on click use me.hide

[paulked]

I would open the file with the VBA editor window open. Alt-Tab to the VBA and then click the square Reset button to close the form. That stops users from not complying with your request by having a cancel button. Or you could protect the cancel with a password!

The other way would be to save the file with the same name and then unhide the shHiddenData, delete the 1 from A1 hide the sheet and delete the name that was inserted on your cover page.

[jcc88]

My code isn't working.

Option ExplicitPrivate Sub Workbook_Open()
    If shHiddenData.Cells(1, 1) = 1 Then Exit Sub
    frmGetName.Show
    Me.Hide
End Sub

I added a cancel button to the userform but upon opening the workbook, it gets stuck on the first line

Private Sub Workbook_Open()

[paulked]

It won't! Didn't you see the new line between Option Explicit and Private Sub?

You need to learn some basics!

Option Explicit

Private Sub Workbook_Open()
    If shHiddenData.Cells(1, 1) = 1 Then Exit Sub
    frmGetName.Show
    Me.Hide
End Sub

[jcc88]

Thanks. I'm trying, but work has more demands than the time I have to learn step by step. For now it's google, youtube and forums

[SamT]

The above should only prompt them once. Once it is saved,re-named and the applicable person is chosen, this should not open up again anytime they go into this workbook.

Option Explicit

'list in this Constant the Login names of all persons allowed to maintain the workbook
Const ITTechUserNames As String = "ITTech1, ItTech2, ITtech3"

Private Sub Workbook_Open()
'If an allowed maintainer, then exit the sub and don't show the UserForm
If Instr(ITTechUserNames, Application.UserName) <> 0 Then Exit sub

'If this book has not been saved as a different name then show the UserForm
If Not Me.Name = "Original Workbook Name" Then UserForm.Show
End Sub

Alternately, put the ITTechUserNames code in the UserForm, and if it is an authorizes user, then Enable the Cancel Button on the Form.

The second thing is, once they have done the above, there will be another prompt for them to choose their name from a drop down list that will auto populate their name and details (email, address, phone, title) into 1 cell on the cover page.

You can use Application.UserName in the UserForm, to select the right Data from Paulked's shHiddenData. Just list the Login names in Column A and offset the rest of the data to the right

[SamT]

Personally, I would use Paulked's hidden sheet, and

Option Explicit

Private Sub Workbook_Open)_
   If Not Me.Name = "Original Workbook Name" then UserForm.Show
Exit Sub

On the hidden sheet, I would add an Authorized Maintainer column. In the user Form I would first use Application.Username to return the correct Data from the hidden sheet, and if there was an "X" or "Y" or something in the Authorizing Column, then I would enable the Cancel button on the UserForm.

Since all the User Data is autofilled when the Form opens, the only Command Buttons, (Enabled,) most Users would see would be a "Information Verified" and a "Wrong Information" Button. If they clicked the Wrong Info button, I would show them a list of Login Names to select one from, and refill the data using their selection.

The Save As Code

Private Sub CButVerified_Click()
   Dim fName As String
   
   Do
      fName = Application.GetSaveAsFilename    
   Loop Until fName <> False

   ThisWorkbook.SaveAs Filename:=fName

   Unload Me
End Sub