Odd behavior of sub in Excel userform.

[TaylorDavidI]

I've been working on this spread sheet for a few months now, mostly because it's not critical and I'm just doing it in my spare time.

The section of code in question works fine under most circumstances; however, when "ClassBox" is not empty then it's failing to call the "Feats" sub on the final iteration of the loop (the only time when ClassBox is checked).

Private Sub Finish(TotHD As Integer)
    Dim x As Integer
    
    Call GeneralInfo
    AbilitySelect.Show
    
    Sheet2.Unprotect myPassword
    For x = 1 To TotHD
        If x = 1 Then
            If TypeBox.Value <> "Humanoid" Or HDbox.Value > 0 Then InitialProf TypeBox.Value
            InitialProf RaceBox.Value
            If RaceBox.Value = "Human" Then
                Load AddFeat
                AddFeat.SetAvailable
                AddFeat.Show
            End If
        End If
        If x = TotHD And ClassBox.Value <> "" Then
            Sheet2.Cells(5, 2).Value = ClassBox.Value
            Sheet2.Cells(5, 10).Value = 1
            InitialProf ClassBox.Value
            SkillPoints (x, TotHD)
            If ClassBox.Value = "Fighter" Then
                Load FighterFeat
                FighterFeat.SetAvailable
                FighterFeat.Show
            End If
            If ComboBox1.Value = "Expert" Then
                Load ExpertSkills
                ExpertSkills.Show
            End If
            'Feats x
        Else
            Sheet2.[J4].Value = CInt(Sheet2.[J4].Value) + 1
            SkillPoints (x, TotHD)
            'Feats x
        End If
        Feats x
    Next x
    Sheet2.Protect myPassword
End Sub

Private Sub Feats(HD As Integer)
    If HD = 1 Or IsDivis(HD, 3) Then
        Load AddFeat
        If HD <= CInt(HDbox.Value) Then
            AddFeat.SetRace RaceBox.Value
        Else
            AddFeat.SetClass ClassBox.Value
        End If
        AddFeat.SetAvailable
        AddFeat.Show
    End If
End Sub

All of the other sub calls work perfectly on every iteration of the loop. I've also tried placing the call to Feats in the If statement itself (where I have them commented out now).

I placed some debugging boxes before the call to Feats and at the beginning of Feats, neither even triggered when ClassBox was non-empty.

The function is called by one of four command buttons on a userform when they're completed running their code.

Can anyone tell me what might be causing that line of code to not run? And my appologies for the terrible style and inefficiency. I'll be working on that once I get it working.

David I. Taylor
A+, Network+, MCP Windows XP

 

[Rofeu]

Hi,

I spoke to soon, apologies, .Hide does not unload the userform. Interestingly enough though, even after unloading a userform in VBA, it can still be communicated with.

As for the Me, I've had it happen that when opening a modeless userform from another (modal) userform, the Me.Hide in the modeless form closed the modal userform instead of the modeless form the code was in. Might just have been glitch though.

 

[TaylorDavidI]

If the three are all equivalent; why does one give an error and the other works fine. I'm just confused, if it works it works but it may be good to know in the future.

David I. Taylor
A+, Network+, MCP Windows XP

 

[fumei]

Hi Rofeu,

We may want to put this into a new thread, but....

even after unloading a userform in VBA, it can still be communicated with.

What exactly do you mean by this?

If you Unload a userform, you can certainly make calls to procedures in the code module of that userform. It is a project code module, just like any other. Is that what you mean by communicating?

faq219-2884

Gerry
My paintings and sculpture

 

[Rofeu]

Hi,

I mean that I can still manipulate ActiveX components on the userform. Maybe I'm just confused as to what you meant by communicating with a userform.

Cheers,

Roel

 

[combo]

It's close to dimming variable 'As New ...'. This is why the second way of working with user forms is preferred:

Sub Method1()
With UserForm1
    .Label1.Caption = "test"
    .Show
End With
MsgBox UserForm1.Label1.Caption
End Sub

Sub Method2()
Dim frmInstance As UserForm1
Set frmInstance = New UserForm1
With frmInstance
    .Label1.Caption = "test"
    .Show
End With
MsgBox frmInstance.Label1.Caption
Set frmInstance = Nothing
End Sub

combo

 

[fumei]

Both those are still using .Show, therefore the form is loaded.

faq219-2884

Gerry
My paintings and sculpture

 

[Rofeu]

Sub FillBox()

Unload UserForm1

UserForm1.TextBox1.Value = "BOOH!"

MsgBox UserForm1.TextBox1.Value

End Sub

The messagebox will still say "BOOH!"

Cheers,

Roel

 

[combo]

It is not THAT userform!
Consider an UserForm1 form as a class module with the designer window. This class can be automatically instantiated by object with the same name (UserForm1.Show), in this case vba does quite a lot to hide what really happens inside.
The second option is to dim an object variable with any other name this class instance, and next create it. My above first procedure is more or less equivalent to:

' class module named 'Class1':
Public Label As String

' standard module
Sub Method1a()
Dim Class1 as New Class1 ' this is close what vba does behind the scenes for UserForm1
With Class1
    .Label = "test"
    MsgBox Label
End With
Set Class1=Nothing ' this is equivalent to terminating userform
MsgBox Class1.Label
End Sub

Your code works this way:

Sub FillBox()
Dim UserForm1 As New UserForm1 '<= vba action, create object when first time calling it (Show method)
...
Unload UserForm1 ' = Set UserForm1=Nothing 
UserForm1.TextBox1.Value = "BOOH!" 'new instance of UserForm1 named UserForm1
' set its TextBox1 value to "BOOH!" 
MsgBox UserForm1.TextBox1.Value ' show TextBox1 value of the line above created UserForm1
End Sub

combo

 

[PHV]

Rofeu, have you tried this ?

Sub FillBox()
UserForm1.TextBox1.Value = "BOOH!"
Unload UserForm1
MsgBox UserForm1.TextBox1.Value
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886