Solved: Spin Up/Down Problem

[jwise]

I'm using an initialize routine to set Public variables. When my Spin Up/Down code is executed, the initialized variables are not there. I assume I am leaving out something. Any ideas?

Here is some sample code:

[vba] Public maxSourceRow as Long

Private Sub SpbSource_SpinUp()

Dim wsIn As Worksheet

With Me.spbSource
If .Value > maxSourceRow - 1 Then
.Value = maxSourceRow - 1
End If
Me.lblRowS.Caption = .Value
End With
End Sub [/vba]
[vba]Public maxSourceRow as Long
Sub frmInit()
...
maxSourceRow = LastRowWithData(wsSource)
...
[/vba]

The program was traced and LastRowWithData is returning the correct number of rows. When the "spin up" arrow is pressed, the error is received indicating there is no value for maxSourceRow.

The Spin routines are located in the "userform" code area, and frmInit is in "Module1".

TIA

[Kenneth Hobs]

I don't see why it wouldn't work. Use Debug.Print to show the value in both subsand check the Immediate window after playing.

Attach a short example xls if you like.

[jwise]

I have attached a workbook with a demo program with this problem. I am using Excel 2003. The demo has a single workbook with a single worksheet. This worksheet has no data. By pressing ALT-F8, a single macro is available; it is called "initTest". There are 3 macros in the userform object; a spin up/down and a "enter" event which tells the form to exit.

When the code is started, it passes the value "5" to the userform, but the userform displays "0". Use the spin buttons to change to any value (1-10) but not "5". Then press the "Quit" button. Again a MsgBox macro tells the value of the public variable. This demonstrates the problem I have of passing back and forth variables from event code to module code.

Thanks again for looking at this.

[Mahahaava]

Hi,

A public "declaration" is only done once. Remove it from the form and it will work.

/Petri

[jwise]

I don't think I would have ever figured this one out. Thank you.

[Mahahaava]

No Prob, Any Time

Cheers,

Petri