How to do not show/terminate an userform, if a boolean ist false

[SeniorNewbie]

Hi out there,

my userform is set up with a dynamic caption. The value is taken from a CustomDocumentProperty (CDP) and counts the number of saved versions of the workbook. The update of the CDP = CDP + 1 is triggerd by saving the file. The status saved/not saved I take from workbook properties. To here everything works fine.

Now I want to block/terminate/hide my userform, if the file ist not yet saved and let the user know by a msgbox (what works as well). My attemps zu make this in the userform_initialize code weren't successfull. The userform calls this code (see below) in a module but appears anyway.

Sub isSaved()
Dim isSaved As Boolean

isSaved = ThisWorkbook.Saved

If isSaved = False Then
    MsgBox "The workbook must be saved first", , isSaved
    UserForm1.Hide
    Exit Sub
End If


End Sub

Any ideas? THX a lot in advance!

Senior

 

[SeniorNewbie]

Ok, obviously it's not that easy to solve... but in the meantime I've got an idea, that may help: Set up not only the caption of the userform (USF) dynamically, set up the entire userform this way. That means instead of hide/terminate the USF I will just delete it. And when it's needed (aka on demand) it will be only created by a dynmically setup for the runtime it's in use AND of course the workbook was positively saved before. I guess that might be a useable workaround and I'm sure that I will make it this. But if there's a little less violent way, I'd be happy to learn it.

THX for the attention und a good night to everybody from Germany ...
Senior

 

[dmt32]

Hi,
try changing your sub to a function that returns a boolean value

Function IsSaved() As Boolean
    IsSaved = ThisWorkbook.Saved
    If Not IsSaved Then MsgBox "The workbook must be saved first", 48, "Not Saved"
End Function

calling it from your UserForm_Initialize event you can terminate the userform show if function returns False

Private Sub UserForm_Initialize()
    'terminate show if workbook has not been saved
    If Not IsSaved Then End
    
    'rest of code
End Sub

Note: End Statement clears all variables

Dave

 

[SeniorNewbie]

Hi Dave,

please let me appologize for my late reply. But it is very short and simple: Your solution works!! Thanks a lot!!

Senior

 

[dmt32]

Hi Dave,

please let me appologize for my late reply. But it is very short and simple: Your solution works!! Thanks a lot!!

Senior

No worries glad resolved your issue & appreciate your feedback

Dave