VBA to popup a window when saving & revision track

[Martines91]

Hi all,

Can anyone help me with this one? I have created a userform to popup when anyone would try to save the file. What I need is whenever someone presses CTRL+S or clicks the save button a popup window would appear (my userform), where they would have to click a radio button to acknowledge how the file was changed, to track revisions (Major, Minor, Miscellaneous or No Change). Depending which would be highlighted, the revision number would change on the sheet. Also is excel able to extract user ID from the PC? Because I would like to keep track of who did the last change.

I found this code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

SaveForm.Show

End Sub

Which actually works and before save it shows the form. But the form have a proceed to save button which does

Private Sub Button_Save_Click()

ThisWorkbook.Save

End Sub

But that then loops back to beforesave, How would I bypass this and proceed to save?

And about the revision tracking, it starts at 1.0.0, is it possible to have that number in 1 cell or does it need to be 3 different cells in order to change the numbers independently? With my basic knowledge I would probably do it like this

Sub OptionMajor_Click()
Sheet1.Range("AE58").Value = Range("AE58").Value + 1
End Sub

But this would mean that whenever I choose that button it will automatically increase the value, I need it to do it only if Save button is clicked.

Any help would be appreciated

[bulina2k]

Hi there !
You don't need to recall the saving of the workbook with this

Please Login or Register  to view this content.

as the code in the Workbook_BeforeSave continues with the saving after you close/unload the SaveForm. The button on the saving details should only unload the form.
Also you could try to use Application.username in order to get the username.

[Martines91]

But if I remove that I can't continue in the form, since the save button is disabled.

[bulina2k]

You should not remove the code under the button, but change it to

Please Login or Register  to view this content.

This will close the SaveForm and now Before_Save will continue to save the workbook.

[yujin]

Hi, Martines91

But that then loops back to beforesave, How would I bypass this and proceed to save?

Set Application.EnableEvents to False and save the book. Then, set Application.EnableEvents back to True.

Please Login or Register  to view this content.

[yujin]

Also is excel able to extract user ID from the PC?

Please Login or Register  to view this content.

[Martines91]

That works, it gets my ID, what I want now is to show that ID in a text box which is disabled.

Private Sub CDSIDTextBox_Change()

CDSIDTextBox.Enabled = False
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")
MsgBox WshNetworkObject.UserName
Set WshNetworkObject = Nothing


End Sub

I've tried this, but this doesn't work as well, the text box only gets disabled when I try to type in it and then a popup window will show with my ID, so instead of that, how would it show straight away with disabled textbox? In which afterwards would rewrite cell "AG56" after each save? I guess that then it should go to the save button?

[yujin]

I guess that then it should go to the save button?

Yes, I think so.

Please Login or Register  to view this content.

[Martines91]

Thank you a lot! Works like a charm, just last thing, I disabled the textbox and I want to show the users ID from PC as mentioned, it works when saving it writes the cell that's fine I just need to have it shown in the form as well, although somehow it currently works with your previous formula but it only shows for a fraction of a second when save button is clicked

I'm using this:

Sub ShowUserName()

Dim WshNetworkObject As Object

Set WshNetworkObject = CreateObject("WScript.Network")

CDSIDTextBox.Value = WshNetworkObject.UserName

Set WshNetworkObject = Nothing
End Sub

How would I have it shown before pressing save?

Thank you

[yujin]

Why don't you use Label instead of TextBox to show the users ID?

Please Login or Register  to view this content.

[Martines91]

Thanks, I just did that but it's the same result, only shows after I click save button and not when the form loads

[yujin]

So you want users ID to show in textbox when the form loads.
Then you should use UserForm_Initialize event.

Please Login or Register  to view this content.

[Martines91]

Brilliant! Thank you for your help, it's working as intended, I'm closing this