Reputation: 47
I know there are a lot of similar questions, but none of them have helped my specific issue. I have been teaching myself VBA and began learning Userforms from http://www.excel-easy.com/vba/userform.html
Everything worked fine and dandy until out of the blue, I received the 424 Error code when I tried to run my Userform after making some tweaks
Private Sub CommandButton21_Click()
StartUpUserForm.Show
End Sub
is the current non-working edition of this, but I have double and triple checked that the userform is called out correctly, deleted and recreated the button half a dozen times, renamed and recalled out the userform in the code and absolutely nothing helps.
The current userform is
Private Sub Userform_initialize()
'Empty NameTextBox
NameTextBox.Value = ""
'Empty Race_Select
Race_Select.Clear
'Fill Race_Select
With Race_Select
.AddItem "Dwarf"
.AddItem "Elf"
.AddItem "Gnome"
.AddItem "Half-Elf"
.AddItem "Half-Orc"
.AddItem "Halfling"
.AddItem "Human"
.AddItem "Other"
End With
'Empty Class_Select
Class_Select.Clear
'Fill Class_Select
With Class_Select
.AddItem "Barbarian"
.AddItem "Bard"
.AddItem "Cleric"
.AddItem "Druid"
.AddItem "Fighter"
.AddItem "Monk"
.AddItem "Paladin"
.AddItem "Ranger"
.AddItem "Rogue"
.AddItem "Sorceror"
.AddItem "Wizard"
End With
'Empty Attributes
Strength.Value = ""
Dexterity.Value = ""
Constitution.Value = ""
Intelligence.Value = ""
Wisdom.Value = ""
Charisma.Value = ""
End Sub
Private Sub Roll_Attributes_Click()
Dim Strength As Integer
Dim Dexterity As Integer
Dim Constitution As Integer
Dim Intelligence As Integer
Dim Wisdom As Integer
Dim Charisma As Integer
'Roll Attributes
Strength = Int((18 - 3 + 1) * Rnd + 3)
Dexterity = Int((18 - 3 + 1) * Rnd + 3)
Constitution = Int((18 - 3 + 1) * Rnd + 3)
Intelligence = Int((18 - 3 + 1) * Rnd + 3)
Wisdom = Int((18 - 3 + 1) * Rnd + 3)
Charisma = Int((18 - 3 + 1) * Rnd + 3)
'Display Integer as attribute
StrengthTextBox.Text = Strength
DexterityTextBox.Text = Dexterity
ConstitutionTextBox.Text = Constitution
IntelligenceTextBox.Text = Intelligence
WisdomTextBox.Text = Wisdom
CharismaTextBox.Text = Charisma
End Sub
(I know it's like painting the Mona Lisa with a brick, but that's not the question here)
This issue popped up earlier today, but I was able to stave it off by renaming my userform and recalling it out with the button code. However, it came back after I renamed the attribute text boxes, and seems to be back for good.
Upvotes: 2
Views: 531
Reputation: 71227
I assume the StartupUserForm
exists and is named as such.
Forms are special types of classes, that have a predeclaredId - a "default instance". When you do this:
Private Sub CommandButton21_Click()
StartUpUserForm.Show
End Sub
You are using that default instance. You should be creating a copy of it:
Private Sub CommandButton21_Click()
Dim frm As StartUpUserForm
Set frm = New StartUpUserForm
frm.Show
End Sub
This will make the CommandButton21
click handler create an object, call the Show
method on it, and then the object will die when the form is closed.
So why are you getting that error?
Private Sub Userform_initialize()
Every class has a special Initialize
event that runs when an instance is created - when you use a form's default instance, code in that procedure may or may not run, depending on the state of your VBA environment. That's why it's better to always create an object, so the code you write in the Initialize
handler will always run when you intend it to.
As @TimWilliams pointed out, we don't know which line is blowing up with a #424 error, so it's pretty hard to help you here.
I suggest you add an error handler to help you figure out what's going on:
Private Sub Userform_initialize()
On Error GoTo CleanFail
'your code here
CleanExit:
Exit Sub
CleanFail:
Debug.Print Err.Number, Err.Description
Stop
Resume 'Resume CleanExit
End Sub
What will that do for you? When an instruction blows up, the VBA editor (VBE) will have the error number and description in the immediate pane (Ctrl+G to show it if it's not visible) and will highlight the line that says Stop
, and then you can press F8 to step through to the next line - VBE will highlight the instruction that says Resume
, and then pressing F8 again will take you straight to the line that caused the error.
Odds are, you have renamed the controls on the form, but not in the code; the Name
property of a control is the identifier you should be using in code to refer to that control.
Your code isn't consistent between the two handlers - in the Click
handler it seems to be StrengthTextBox
...
StrengthTextBox.Text = Strength
...and in the Initialize
handler it seems to be just Strength
:
Strength.Value = ""
If the control/textbox is actually called Strength
, then you have another problem:
Dim Strength As Integer
Strength = Int((18 - 3 + 1) * Rnd + 3)
The variables have the same name as the textboxes, and that name clash confuses VBA - it's possible VBA interprets this assignment as assigning an integer value to an object reference, which is bound to blow up.
Bottom line, verify that your control names match the identifiers you're using to refer to them in the code.
Upvotes: 4
Reputation: 5770
Your problem lies in the block of code within the Initialization event that is below 'Empty Attributes.
What are these attributes? You assign to them a value as if they were objects, but they are not declared anywhere. If they are meant to be variables, declare them as such, and initialize them using Strength = 0
.
Does this help?
EDIT: I just noticed that you are declaring the variables in the Click event for the command button. This raises an additional issue about Variable Scope. If you were to declare the variables (even if they use the same name) in the Initialize event, they will not correspond to the variables in the Click event because they are privately scoped. You should remove the references to the attributes entirely in the initialization event. Otherwise, you can look into Publicly declared variables.
Upvotes: 0