Rotation number on a form

Hello Ronnie,

Sorry but I don't know what a rotation number is. Can you explain and maybe I (or someone else) can help.

Regards,
Dax.

Hello Dax,

With a rotation number I mean a number that will automatically switch 1 place up when I've saved it. Like when it starts at 2000 and after saving it must be 2001 and after saving again 2002. I hope you understand what I mean? Hope to have a respons of you soon. Thanks again.

RONNIE

Hi,

There are a couple of ways I can think of. You could store the number on a worksheet and increase the number each time the workbook is saved. This has the disadvantage that someone might come along and just change it. A better method is to use the registry, which is a database used to store various settings used by Windows applications. This code will search for a number in the registry and use it to populate a label (Label1) on a form (Userform1). Obviously, you'll have to customise the code to meet your needs. To use this code open the VB Editor, click View, Project Explorer and then double click the ThisWorkbook icon. Then paste the following code:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Rotation = CLng(GetSetting("App Name", "Section", "KeyName", "-1"))
Rotation = Rotation + 1
VBA.SaveSetting "App Name", "Section", "KeyName", Rotation
End Sub

Private Sub Workbook_Open()
Dim Test As String
'Get the rotation number from the registry
Test = VBA.GetSetting("App Name", "Section", "KeyName", "0")
If Test = "0" Then 'The key doesn't exist
VBA.SaveSetting "App Name", "Section", "KeyName", "2000"
Rotation = 2000
Else
'Registry keys are string so it needs to be converted
'to a number using the CLng function.
Rotation = CLng(Test)
End If

Load UserForm1
UserForm1.Label1.Caption = Rotation
UserForm1.Show
End Sub

I hope this helps,
Dax.