D&D AC Form

I've created a UserForm with multiple fields. How do I take those fields and have their values added to specific cells in a worksheet?

This question may be easier to answer if I explain exactly what this sheet is doing. I play D&D and am creating a sheet to help me run the game more efficiently. Currently, I am working on the AC side of things. So, the form I have created is a multi-page form. Page 1 is where I enter all of their base stats for AC, the stats that only change when new equipment is found for the most part. Page 2 is the 'buff' page where I add things like a spell that boosts a certain part of AC for 5 rounds or something like that.

I have already designed the spreadsheet formulas to calculate AC properly, since some of these values stack but most of them do not.

How do I link this form to the sheet? I need it to where when I click the button that brings up the form, it has the information from the sheet listed and ready to edit. I would like to have one button and one form, with a drop down field in that form that will switch from player to player.

Hope this isn't too complicated. Most likely, if I can figure out how to link text fields to certain cells, and then change that link with drop down menus (once I figure out how those work as well), then I can figure the rest out on my own... most likely, anyway. ;-)

To populate a textbox from a cell use something like this.

Please Login or Register  to view this content.

To populate a cell from a tetxbox try something like this.

Please Login or Register  to view this content.

If you want to populate when the form is opened put the code in the Initialize event of UserForm.

If you have a dropdown to choose a player use it's Change event to re-populate.

OK, I hope I am not just overlooking the obvious, but I cannot get anything to work in the form initialize routine. I just want it to pull up data when I open the form. I know how to set the individual text boxes to do that, but the problem is that I want to be able to change their values when I choose a different entry in my drop down list box. Currently, I am just playing around with the initialize process and will move onto the rest when I have this part work. Can someone look at this code and tell me what I am doing wrong? (Please keep in mind that I am just beginning to use VBA).

The end result on the form is that I want the changes made in the text boxes to change their corresponding cells when I hit the OK button or discard them when I hit cancel... Although I am not really going there until I can figure out this initialize thing.

Thanks,
Scott

Private Sub ArmorClass_Initialize()
TxtArmorBase.Value = ActiveWorkbook.Sheets("AC").Range("E3").Value
TxtArmorEnhanceBase.Value = ActiveWorkbook.Sheets("AC").Range("F3").Value
TxtShieldBase.Value = ActiveWorkbook.Sheets("AC").Range("G3").Value
TxtShieldEnhanceBase.Value = ActiveWorkbook.Sheets("AC").Range("H3").Value
TxtNaturalBase.Value = ActiveWorkbook.Sheets("AC").Range("I3").Value
TxtNaturalEnhanceBase.Value = ActiveWorkbook.Sheets("AC").Range("J3").Value
TxtDeflectionBase.Value = ActiveWorkbook.Sheets("AC").Range("K3").Value
TxtDodge.Value = ActiveWorkbook.Sheets("AC").Range("L3").Value
TxtDexterityBase.Value = ActiveWorkbook.Sheets("AC").Range("M3").Value
TxtWisdomBase.Value = ActiveWorkbook.Sheets("AC").Range("N3").Value
TxtSizeBase.Value = ActiveWorkbook.Sheets("AC").Range("O3").Value
TxtPrestige1Base.Value = ActiveWorkbook.Sheets("AC").Range("P3").Value
TxtPrestige2Base.Value = ActiveWorkbook.Sheets("AC").Range("Q3").Value
TxtPrestige3Base.Value = ActiveWorkbook.Sheets("AC").Range("R3").Value
End Sub

The name of your Initialize sub is wrong.

It should be.

Please Login or Register  to view this content.

You really shouldn't try writing the code shells for events yourself, use the dropdown menus provided by the VBA editor.

If you do that you should avoid errors.

It doesn't matter what the name of the form.


The correct syntax for the Initailize Event is what I said.

Did you try it?

Yes, I made the change and now the form causes an error when I click the AC button. The error is:

Run-Time error '424': Object Required.

The code for this button is:

Private Sub AC_Click()
ArmorClass.Show
End Sub

The debugger highlights the ArmorClass.show line.

I also changed the name of the form to the Generic UserForm and the code to the following:

Private Sub AC_Click()
UserForm.Show
End Sub

But, I still get the error. I have changed the code back to what I had originally and the form comes up, although none of the code in the initialize section works and all of my textboxes are blank.

You did not need to change the name of the form.

Double click on the form (or right click and select View Code).

Now from the left dropdown choose UserForm (notice your form name is not listed) and from the right dropdown choose Initialize.

You should now see the following code stub.

Please Login or Register  to view this content.

This is the same for all userforms.

So your code should look like this and should run when you open the form.

Please Login or Register  to view this content.

Which line is highlighted when you get the error and click Debug?

Check you have the correct names for the controls/worksheets etc.

Please Login or Register  to view this content.

This is the line that the debugger highlights:

Please Login or Register  to view this content.

and the error it gives me is:

Run-time error '424':
Object Required.

I changed my initialize code to what you had in your last post and got this error:

Run-time error '-2147024809 (80070057)':
Could not find the specified object.

and again the exact same line is highlighted as the problem.

Can you please check that you have the correct names for all forms and controls.

about an hour after posting that, I found the error... there was one of the textboxes misspelled by one letter. When the debugger kept highlighting the ArmorClass.Show line, I kept assuming that was where the problem lay. I actually found the issue when I was looking for something else. Sorry for all of the confusion, but thanks for all the help!

Glad you found, and fixed, the problem.

A tip for the future, pressing CRTL+Spacebar when you are writing code in the VBA editor should present you with a dropdown list which includes the names of your controls.

Thanks for that tip, Norie. That should really help.

Now that I have that fixed, it's on to the next feature. Instead of having a lot of If statements repopulating the text boxes when I change the combo box, is there a function or way I can program the form so that it works like the vLookup function? Instead of if the combobox equals this cell, then do this... something like lookup combobox value and then return the appropriate values. And then have it to where when I hit the OK button, it would write the values back to the correct row.

The form is completely operational, but so loaded with code that it is quite slow. Plus, it is limited to six players, and I want to be able to have that number dynamic and not have to add code if I add a seventh player, you know?

I want to design an Add Character form, and then have that new character automatically show up in the drop down menus on the AC form. I also think it will end up being less code that way and probably much faster.