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

 

[TaylorDavidI]

If only it were that simple Skip, and yes we all make those mistakes.

No I've had it work for iteration 1 and 2 then when it hits 3 and it should open AddFeat again fail when there is something in ClassBox. I've also had it go properly through 12 iterations when there is nothing in ClassBox.

Not to mention part of the code which calls this sub checks to make sure TotHD isn't < 1

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

 

[SkipVought]

Well, thangs were goin' kinda sloooow here in cowtown, an' it got me ta thinkin'.

Skip,
[sub]
[red][/red]
[/sub]

 

[fumei]

No I've had it work for iteration 1 and 2 then when it hits 3 and it should open AddFeat again fail when there is something in ClassBox. I've also had it go properly through 12 iterations when there is nothing in ClassBox.

Sorry, but can we get a description of precisely what is not working?

This is the first mention of AddFeat not working. From your first post:

when "ClassBox" is not empty then it's failing to call the "Feats"

Feats.


Not Addfeats.

Iteration 1, iteration 2, iteration 3. You mean x = 1, x = 2 work, but x = 3 does not?

Have you tried my Select Case suggestion?

Gerry
My paintings and sculpture

 

[TaylorDavidI]

AddFeats is loaded by Feats, per the code I posted. Which would mean that the sub was called properly. Oh, the IsDivis function is one I made that checks to see if the first argument is divisible by the second argument.

What happens is that when I go into the for x = 1 to TotHD loop with something like TotHD = 3 and ClassBox = "some value".

On x = 1 the entire code runs as expected, the same on x =2. When it comes to x = 3, It sets the cells in Sheet2 properly, runs InitialProf without any erros, calls SkillPoints and runs that fine. After that, instead of running the Feats sub it just closes down the user form.

No, I haven't tested the select case yet, still don't have the file on hand. Sorry for the misleading statement but to me "afternoon" begins around 5 when I get back from my "lunch" break.

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

 

[fumei]

OK. In your OP, you stated the issue was

it's failing to call the "Feats" sub

Now, if I understand correctly, you are saying that the userform closes. Yes? It is not just a failure to call Feats, but your userform closes. Right?

Certainly if the userform actually closes, then it is not going to call Feats!

OK, what happens if you comment out the instructions before the Feats(x) call? These are loading other userforms. They are getting focus. Is focus being properly handed back to the original userform?

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)

Try commenting out these other userforms. If x = 3 (from the loop), and TotHD = 3 (so If x = TotHD is True) And ClassBox.Value = "some value (so <> "" is True), does Feats get called? Assuming you remove the comment from Feats(x).

Gerry
My paintings and sculpture

 

[TaylorDavidI]

I've tried out the case statement, but I don't know if it's handling things properly because the problem is occuring sooner now. So I guess you were right, I'm a little new to VBA so I don't know how to properly hand focus back.

The reason I assumed the line simply wasn't being called is because it was only happening on the last loop, after which the code returns to the command button and hits an "Unload Me".

With the case statement in the userform is closed on the first loop right after running SkillPoints (this also loads another userform). Previously this userform closed itself without issue the first few times through the loop (x=1 through x=TotHD-1) so I wasn't even looking for problems there.

The new code looks like this:

Private Sub Finish(TotHD As Integer)
    Dim x As Integer
    
    Call GeneralInfo
    Load AbilitySelect
    AbilitySelect.Show
    
    Sheet2.Unprotect myPassword
    For x = 1 To TotHD
        Select Case x = 1
            Case Is = 1
                If TypeBox.Value <> "Humanoid" Or TotHD > 1 Then InitialProf TypeBox.Value
                InitialProf RaceBox.Value
                If RaceBox.Value = "Human" Then
                    Load AddFeat
                    AddFeat.SetAvailable
                    AddFeat.Show
                End If
                If TotHD = 1 Then
                    Sheet2.[B5].Value = ClassBox.Value
                    Sheet2.[J5].Value = 1
                    InitialProf ClassBox.Value
                Else
                    Sheet.[J4].Value = CInt(Sheet2.[J4].Value) + 1
                End If
            Case Is = TotHD
                If ClassBox.Value <> "" Then
                    Sheet2.Cells(5, 2).Value = ClassBox.Value
                    Sheet2.Cells(5, 10).Value = 1
                    InitialProf ClassBox.Value
                Else
                    Sheet.[J4].Value = CInt(Sheet2.[J4].Value) + 1
                End If
            Case Else
                Sheet.[J4].Value = CInt(Sheet2.[J4].Value) + 1
        End Select
        Call SkillPoints(x, TotHD)
        Feats x
    Next x
    Sheet2.Protect myPassword
End Sub

As I say, this is called by one of four command buttons just before they run an Unload Me.

The other forms which are loaded throughout this process are:

AbilitySelect: which loads one of three forms then unloads.

Forms called by AbilitySelect: None of those forms call any other forms.

InitialProf: this isn't another form, but it is a public sub from another module. If ClassBox is set to "Expert" it will load another form but I haven't tried that yet.

AddFeat: does not load any other forms.

SkillPoints: A sub within the module which loads the AddSkill userform. This form seems to be the problem.

AddSkill: This form does not load any subsequent userforms

Feats: A sub within the module which on x=1 and every x which is divisible by 3 loads AddFeats. I'm no longer getting this far.

I don't see any out of place end statement or exit statements within the AddSkill form. Each form, regardless of how I call it, currently just ends by a command button who's last line is "Unload Me"

For Example the button for this form is:

Private Sub StandardButton_Click()
    Dim RaceHD As Integer, HP As Integer, TotHD As Integer, x As Integer
    
    'calculate total HD
    On Error Resume Next
    TotHD = CInt(HDbox.Value)
    If (ClassBox.Value <> "") Then TotHD = TotHD + 1
    
    'Test for race and HD
    If FailTest(TotHD) Then Exit Sub
    
    'calculate racial HD type
    RaceHD = VLOOKAllSheets(TypeBox.Value, Names("classinfo").RefersToRange, 2, False)
    
    'generate starting hp
    With Sheet17
        If HDbox.Value > 0 Then
            .Cells(2, 3).Value = RaceHD
            For x = 2 To CInt(HDbox.Value)
                Randomize
                .Cells(2, 3).Value = .Cells(2, 3).Value + Int(Rnd() * RaceHD) + 1
            Next x
        End If
        If (ClassBox.Value <> "") Then
            Randomize
            .Cells(2, 3).Value = .Cells(2, 3).Value + Int(Rnd() * HDText.Value) + 1
        End If
        .Cells(2, 6).Value = "standard"
    End With
    
    Call Finish(TotHD)
    
    Unload Me
End Sub

The commandbutton for AddSkill is pretty messy but I'll post it anyway since it seems to be the source of my problems at this point.

Private Sub CommandButton1_Click()
    Dim temp As String
    
    If TextBox171.Value < 0 Then
        MsgBox "Not enough skill points"
        Exit Sub
    End If
    If TextBox171.Value > 0 Then
        MsgBox "You still have points"
        Exit Sub
    End If
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    With Sheet4
        .Unprotect myPassword
        .Cells(2, 5).Value = TextBox3.Value
        .Cells(3, 5).Value = TextBox6.Value
        .Cells(4, 5).Value = TextBox9.Value
        .Cells(5, 5).Value = TextBox12.Value
        .Cells(6, 5).Value = TextBox15.Value
        .Cells(7, 5).Value = TextBox18.Value
        If .Cells(7, 1).Value = "Craft (" And TextBox18.Value > 0 Then
            Do
                temp = InputBox("What type of items can you craft?")
            Loop While temp = ""
            .Cells(7, 1).Value = .Cells(7, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(8, 5).Value = TextBox21.Value
        If .Cells(8, 1).Value = "Craft (" And TextBox21.Value > 0 Then
            Do
                temp = InputBox("What type of items can you craft?")
            Loop While temp = ""
            .Cells(8, 1).Value = .Cells(8, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(9, 5).Value = TextBox24.Value
        If .Cells(9, 1).Value = "Craft (" And TextBox24.Value > 0 Then
            Do
                temp = InputBox("What type of items can you craft?")
            Loop While temp = ""
            .Cells(9, 1).Value = .Cells(9, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(10, 5).Value = TextBox30.Value
        .Cells(11, 5).Value = TextBox33.Value
        .Cells(12, 5).Value = TextBox36.Value
        .Cells(13, 5).Value = TextBox39.Value
        .Cells(14, 5).Value = TextBox42.Value
        .Cells(15, 5).Value = TextBox45.Value
        .Cells(16, 5).Value = TextBox48.Value
        .Cells(17, 5).Value = TextBox51.Value
        .Cells(18, 5).Value = TextBox54.Value
        .Cells(19, 5).Value = TextBox57.Value
        .Cells(20, 5).Value = TextBox60.Value
        .Cells(21, 5).Value = TextBox63.Value
        .Cells(22, 5).Value = TextBox66.Value
        .Cells(23, 5).Value = TextBox69.Value
        .Cells(25, 5).Value = TextBox72.Value
        .Cells(26, 5).Value = TextBox75.Value
        .Cells(27, 5).Value = TextBox78.Value
        .Cells(28, 5).Value = TextBox81.Value
        If .Cells(28, 1).Value = "Knowledge (Local:" And TextBox81.Value > 0 Then
            Do
                temp = InputBox("What region do you have knowledge of?")
            Loop While temp = ""
            .Cells(28, 1).Value = .Cells(28, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(29, 5).Value = TextBox84.Value
        If .Cells(29, 1).Value = "Knowledge (Local:" And TextBox84.Value > 0 Then
            Do
                temp = InputBox("What region do you have knowledge of?")
            Loop While temp = ""
            .Cells(29, 1).Value = .Cells(29, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(30, 5).Value = TextBox87.Value
        If .Cells(30, 1).Value = "Knowledge (Local:" And TextBox87.Value > 0 Then
            Do
                temp = InputBox("What region do you have knowledge of?")
            Loop While temp = ""
            .Cells(30, 1).Value = .Cells(30, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(31, 5).Value = TextBox90.Value
        .Cells(32, 5).Value = TextBox93.Value
        .Cells(33, 5).Value = TextBox96.Value
        .Cells(34, 5).Value = TextBox99.Value
        .Cells(35, 5).Value = TextBox102.Value
        If .Cells(35, 1).Value = "Knowledge (Local:" And TextBox102.Value > 0 Then
            Do
                temp = InputBox("What subject do you have knowledge of?")
            Loop While temp = ""
            .Cells(35, 1).Value = .Cells(35, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(36, 5).Value = TextBox105.Value
        .Cells(37, 5).Value = TextBox108.Value
        .Cells(38, 5).Value = TextBox111.Value
        .Cells(39, 5).Value = TextBox114.Value
        If .Cells(39, 1).Value = "Perform (" And TextBox114.Value > 0 Then
            Do
                temp = InputBox("What do you perform?")
            Loop While temp = ""
            .Cells(39, 1).Value = .Cells(39, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(40, 5).Value = TextBox117.Value
        If .Cells(40, 1).Value = "Perform (" And TextBox117.Value > 0 Then
            Do
                temp = InputBox("What do you perform?")
            Loop While temp = ""
            .Cells(40, 1).Value = .Cells(40, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(41, 5).Value = TextBox120.Value
        If .Cells(41, 1).Value = "Perform (" And TextBox120.Value > 0 Then
            Do
                temp = InputBox("What do you perform?")
            Loop While temp = ""
            .Cells(41, 1).Value = .Cells(41, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(42, 5).Value = TextBox123.Value
        If .Cells(42, 1).Value = "Profession (" And TextBox123.Value > 0 Then
            Do
                temp = InputBox("What is your profession?")
            Loop While temp = ""
            .Cells(42, 1).Value = .Cells(42, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(43, 5).Value = TextBox126.Value
        If .Cells(43, 1).Value = "Profession (" And TextBox126.Value > 0 Then
            Do
                temp = InputBox("What is your profession?")
            Loop While temp = ""
            .Cells(43, 1).Value = .Cells(43, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(44, 5).Value = TextBox129.Value
        If .Cells(44, 1).Value = "Profession (" And TextBox129.Value > 0 Then
            Do
                temp = InputBox("What is your profession?")
            Loop While temp = ""
            .Cells(44, 1).Value = .Cells(44, 1).Value & temp & ")"
            temp = ""
        End If
        .Cells(45, 5).Value = TextBox132.Value
        .Cells(46, 5).Value = TextBox135.Value
        .Cells(47, 5).Value = TextBox138.Value
        .Cells(48, 5).Value = TextBox141.Value
        .Cells(49, 5).Value = TextBox144.Value
        .Cells(50, 5).Value = TextBox147.Value
        .Cells(51, 5).Value = TextBox150.Value
        .Cells(52, 5).Value = TextBox153.Value
        .Cells(53, 5).Value = TextBox156.Value
        .Cells(54, 5).Value = TextBox159.Value
        .Cells(55, 5).Value = TextBox162.Value
        .Protect myPassword
    End With
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.Calculate
    Unload Me
End Sub

I hope that clears a few things up?

As you can probably tell by the code this isn't for any business application but I learn things better when I can apply them to something I enjoy and I'm certainly still in the learning stages on this one.

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

 

[Rofeu]

Hi,

I think that the Unload Me at the end of CommandButton1_Click is actually unloading your AbilitySelect.

Try replacing it with AddSkill.Hide.
(within VBA, you don't really need to load and unload userforms, it is incorporated in the Show and Hide commands)

Cheers,

Roel

 

[TaylorDavidI]

Thanks for the idea Roel, but that's a no go either. I replaced every instance of Unload Me in the project with Me.Hide and still the main form is being closed as soon as I'm finished with the first secondary form it loads.

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

 

[PHV]

Have you tried to put some breakpoints and single step your code ?

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

 

[PHV]

Another though: are all the UserForms modal ?

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

 

[fumei]

Roel, are you stating that .Hide incorporates Unload?

Show does incorporate Load, in that .Show will indeed load the userform.

.Hide does not Unload the userform. It removes it from the screen, but the userform is still in memory, is still running, and can still be communicated with, and can still execute processes.

If CommandButton1_Click is on the userform AddSkills, then Me must be AddSkills. AFAIK, Me refers to the parent object of the control, in which case is AddSkills. Why do you think the Unload Me refers to AbilitySkills?

Gerry
My paintings and sculpture

 

[TaylorDavidI]

Not only did using Me.Hide fail to fix the problem but as you say the sheets remained in memory and the next time I called them the old values were in place.

Sorry it took me so long to listen to you about the break points... I'm editing with the built in VBA editor from office and only just realized I still could. The line that apparently crashes the code is this

Sheet2.[J4].Value = CInt(Sheet2.[J4].Value) +1

I've tried removing the CInt, altering the formula instead and so on. The line doesn't complete executing (the cell still shows 0) and there are no errors given before it returns me to the editor window with no break point showing as active.

Is there an easier way to increment a value that might not cause this problem? something similar to java's ++x and x++?

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

 

[TaylorDavidI]

Sorry for the double post, got too busy talking about the code I forget to ask what you meant by "Modal Userforms" PHV and how I would distinguish them from other types?

All I did to create the forms was go into the VB Editory and click "new userform"

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

 

[PHV]

You may try to replace this:
Sheet2.[J4].Value = CInt(Sheet2.[J4].Value) +1
with this:
Sheet2.[J4].Value = Val(Sheet2.[J4].Value) + 1

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

 

[TaylorDavidI]

Stil getting the same results, break point activates when it reaches that line, I press continue and the cell doesn't get updated nor does the following break point trigger.

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

 

[PHV]

break point activates when it reaches that line
And what is displayed when you hoover the mouse pointer on this line ?

Tip: use F8 (single step) instead of F5 (continue) if you really want to debug your code.

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

 

[TaylorDavidI]

I don't get any tool tips from the line and if I right click and choose definition it says "Identifier under cursor not recognized".

If I step through the code from there it skips completely out of the sub back up to the command button that called it and the unload statement which follows.

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

 

[PHV]

And what about this ?
Sheet2.Cells(4, 10).Value = Val(Sheet2.Cells(4, 10).Value) + 1

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

 

[TaylorDavidI]

That seems to have fixed it. Is there a reason that Cells(4,10).Value is any different than Range("J4").Value or it's supposed equivalent [J4].Value?

Now I'm going to have to go through all my code and change it to Cells to make sure I don't run into that again!

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

 

[SkipVought]

objSheet.Cells(4,10)
objSheet.Range("J4")
objSheet.[J4]

are all equivalent object references

Skip,
[sub]
[red][/red]
[/sub]