Excel VBA Userform Global Counter

Question

I have created a VBA UserForm whose purpose is to allow the user to update records held in that Workbook. On Initialization, the UserForm populates with certain cell values in various text boxes, the user is then asked to add certain information in other text boxes, and on clicking the 'Next' button, the new information is added to the data table and the next entry populates the UserForm.

I am currently achieving this by keeping a count of the current row (currentRow) and once the 'Next' button is clicked, currentRow = currentRow + 1. This works fine.

My problem now is that I need to show a different UserForm if the value of a specific cell is different, for example:

If Range(Cells(currentRow, 3).Address).Value = "Planning" Then
    PlanningUserForm.Show
Else
... rest of code ...

This works, but I am struggling to maintain the counter once this new PlanningUserForm is initialized. I need it to know the current value of currentRow so that it can display the correct cell values in its text boxes.

I have declared the variable currentRow as Public, but it doesn't seem to carry over the value into the new userform. Is this possible? Or am I asking too much of Excel?

Many thanks

Matt

Andreas Andreas · Accepted Answer · 2016-09-07T11:04:05

You can always use the registry. It can be used to many other things too in the future.

SaveSetting "Your macro", "Values", "CurrentRow", currentRow

Now the currentRow is saved in your registry.

Now in your PlanningUserForm:

currentRow = GetSetting("Your macro", "Values", "CurrentRow")

and you got the saved value from your registry.

I have bad experiences with global variables, that is why I suggest registry.

Excel VBA Userform Global Counter

2 Answers