Public Sub Workbook_BeforeSave - Passing Variable issue

[myrandr]

OK...long time lurker, have used this board as a resource bible for some time - I am not an expert or prolific programmer in VBA but use often for Excel Dev work.

My issue - I am trying to solicit user information prior to saving, I originally used an inputbox top obtain this is information but is not practical (and honestly looks a little unprofessional) as it only always the single line view of text being entered and the text users are entering can easily be 200+ characters.

I created a nice form, declared my public variables to pass info from user form to the before_save module but the variable do not carry between the user form and before_save modules. I have tested outside of the before_save module and code works fine. Here is a sample of what I got:

This code is in "This Workbook"

Public CancelVar As Variant
Public TextReturn As Variant
Public MsgResult1, MsgResult2 As Variant

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

Dim Msg1, Msg2 As Variant
Dim Row1 As Integer

On Error Resume Next

' Code for version save

ReqEntry:

Load VersionSaveForm
VersionSaveForm.Show

If MsgResult1 = "admin" Then GoTo SkipEntry

If CancelVar = 7 Then
Cancel = True
Exit Sub
Else: End If

If MsgResult1 = "" Then
MsgResult2 = MsgBox("Notes are required, do you wish to cancel save 'No to cancel' or enter notes 'Yes'?", vbYesNoCancel)
If (MsgResult2 = 7 Or MsgResult2 = 2) Then ' Code to cancel save on user request
Cancel = True
Exit Sub
Else
GoTo ReqEntry
End If
Else
End If

- more code after this, but this is where the issue of not passing variable info back and forth lies. The "VersionSaveForm" has very basic code - a text box where user enters information on the version save, a submit button and a cancel button.

Any help would be greatly appreciated.

Regards,

C

 

[RoryA]

Be useful to see the code that sets the variable values...

 

[Andrew Poulsom]

Welcome to MrExcel.

You could also try moving your variable declarations into a General module.

 

[myrandr]

Public Sub CancelButton_Click()

Unload VersionSaveForm

CancelVar = 7

End Sub


Public Sub ContinueButton_Click()

VersionSaveForm.Hide
MsgResult1 = VersNotesText
TextReturn = VersNotesText

End Sub

VersNotesText is the name assigned to the text box on my form...

 

[RoryA]

Public Sub CancelButton_Click()

ThisWorkbook.CancelVar = 7
Unload Me
End Sub


Public Sub ContinueButton_Click()

Me.Hide
ThisWorkbook.MsgResult1 = VersNotesText
ThisWorkbook.TextReturn = VersNotesText

End Sub

since your variables are public members of the ThisWorkbook object. Or, as Andrew said, move them to a normal module.

 

[myrandr]

On the continue button code, I had switched from unloading the form to hiding to see if that would resolve - but it did nothing different.

 

[myrandr]

Moved all Public Variable to their own module, add module tag to each variable (code Rory showed an example of) and works like a charm!

Rory, Andrew, thank you for your quick responses!