Announcement

Collapse
No announcement yet.

TextBox in UserForm only populates on pressing enter.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • TextBox in UserForm only populates on pressing enter.



    I have UserForm with three ComboBoxes (PropertyName,HouseNumber,HotelNumber) that have hard coded options and I have a few TextBoxes (Rent) that I need to populate when a value is selected from each ComboBox. It currently only works when I press enter for each TextBox.


    Code:
    Private Sub Rent_Enter()
    
    
    'MEDITERRANEAN AVENUE
    'houses 1-10
    
    If InStr(PropertyName.Text, "Mediterranean Avenue") And InStr(HouseNumber.Text, "1") And InStr(HotelNumber.Text, "0") Then
    Rent.Value = "10"
    
    Else
    
    If InStr(PropertyName.Text, "Mediterranean Avenue") And InStr(HouseNumber.Text, "2") And InStr(HotelNumber.Text, "0") Then
    Rent.Value = "30"
    
    Else
    
    If InStr(PropertyName.Text, "Mediterranean Avenue") And InStr(HouseNumber.Text, "3") And InStr(HotelNumber.Text, "0") Then
    Rent.Value = "90"
    
    Else
    
    If InStr(PropertyName.Text, "Mediterranean Avenue") And InStr(HouseNumber.Text, "4") And InStr(HotelNumber.Text, "0") Then
    Rent.Value = "160"
    The Code repeats with all options and ends with

    Code:
       End If
    End Sub
    I have tried Changing the Rent_Enter to something else but then it doesn't work at all. Nothing in this UserForm pulls from a spreadsheet. I'm only using it to reference predetermined static values.

    I'm sorry if this is hard to read I absolutely know nothing about VBA everything I have done is from 3 weeks of google searching.

  • #2
    please provide your spreadsheet (without sensitive information.

    Comment


    • #3
      There is no information in the spreadsheet everything is handled or populated through the VBA code.

      Comment


      • #4
        Here is the code for the UserForm itself.

        Code:
        Private Sub UserForm_Activate()
        With PropertyName
        .AddItem "Mediterranean Avenue"
        .AddItem "Baltic Avenue"
        .AddItem "Oriental Avenue"
        .AddItem "Vermont Avenue"
        .AddItem "Connecticut Avenue"
        .AddItem "St. Charles Place"
        .AddItem "States Avenue"
        .AddItem "Virginia Avenue"
        .AddItem "St. James Place"
        .AddItem "New York Avenue"
        .AddItem "Kentucky Avenue"
        .AddItem "Indiana Avenue"
        .AddItem "Illinois Avenue"
        .AddItem "Atlantic Avenue"
        .AddItem "Ventnor Avenue"
        .AddItem "Marvin Gardens"
        .AddItem "Pacific Avenue"
        .AddItem "North Carolina Avenue"
        .AddItem "Pennsylvania Avenue"
        .AddItem "Park Place"
        .AddItem "Boardwalk"
        .AddItem "Electric Company"
        .AddItem "Water Works"
        .AddItem "Reading Railroad"
        .AddItem "Pennsylvania Railroad"
        .AddItem "B. & O. Railroad"
        .AddItem "Short Line"
        End With
        
        With HouseNumber
        .AddItem "0"
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "6"
        .AddItem "7"
        .AddItem "8"
        .AddItem "9"
        .AddItem "10"
        End With
        
        With HotelNumber
        .AddItem "0"
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "6"
        .AddItem "7"
        .AddItem "8"
        .AddItem "9"
        .AddItem "10"
        End With
        
        
        End Sub
        Each ComboBox does not have any code at all

        The only other code is the code for the text box which is what I originally posted. When the options from the ComboBox are selected I need it to push an update to the TextBox within the same UserForm.

        I hope this helps since I don't know what I'm doing its hard to relay what I'm thinking to a way you can understand.

        Comment


        • #5
          attached is a quick and dirty spreadsheet called monopoly.xlsm.

          Attached Files

          Comment


          • #6
            if you do not want the rents in the spread sheet, you could load values into an array.

            Comment


            • #7
              How would I do that? By array do you mean in code? I'm not familiar with VBA at all. I have all of the property information in the code. I just need to be able to have the userform pull the values from the code based on the criteria of the 3 command boxes and input it into text boxes. The code i have works but only when I click on each text box individually and press enter. What I would like to happen is the user form is opened via command button on spreadsheet. The user will select the property being referenced then select how many houses then select how many hotels and then the textbox will spit out how much rent is due within the user form. Everything works except for having to click enter on the textbox. This annoying because I have three other textboxes and have the same problem. Is their a way I can code the text box to look for changes in the comboxes and adjust accordingly?

              Comment


              • #8
                The same thing I did for the one textbox can be done to the other three text boxes.
                what are the other text boxes?

                your spread sheet would be helpful.

                P.S.
                You forgot Tennesse in your list

                Comment


                • #9
                  my apologies when I viewed your document it was on my phone and I had no idea you had macros. would you like the full document or the bit about the user form? The full document is a bit messy and I'm trying to learn ways to clean it up and make it work better but like I said before I'm learning everything I'm doing through google and I'm 3 weeks in.

                  Comment


                  • #10
                    I had to re-upload your file with my UserForm Imported into it because my file was to large.
                    Attached Files

                    Comment


                    • #11
                      here is a new version called newmonopoly
                      Attached Files

                      Comment


                      • #12
                        Thank you. How do I make it so I don't have to use a spreadsheet? and one other thing I have to link the utilities with the value of dice but that will be linked to the sheet. Since I cant upload my file I will upload a screen shot.
                        Attached Files

                        Comment


                        • #13
                          On an additional note I was playing around with your example and my version of monopoly has altered or house rules so to speak that allows up to 10 houses AND 10 hotels on a single property. I would need to be able to include that into the code I might just go with the spreadsheet idea it seems like it would be easier and it looks like you put a lot of work into it. I just didn't want anyone to be able to mess up the document but I can always hide and protect it.

                          Comment


                          • #14
                            I have created A sheet with the raw data.
                            Attached Files

                            Comment


                            • #15


                              sorry about the number of houses and hotels, I just assumed you were using the normal monopoly game setup

                              the spread sheet is MUCH easier to do. (simply hide and protect it)

                              Since you are not following the normal monopoly game (Eg: you are using 10 houses and 10 hotels)
                              I would need you to provide me with:
                              rents for each property
                              rents for each House
                              rents for each Hotel
                              price with no houses and hotels
                              price per house
                              price per hotel
                              mortgage per property

                              I would require this to determine what kind of formula I would need.
                              If the rents are consistent eg: 1 house = 10 2 house = 20 3 house = 30
                              (the same for hotels)
                              then the I can use a formula to calculate it.
                              otherwise I would have to fill the speadsheet with all of them

                              If you could provide me with this info I can let you know

                              Comment

                              Working...
                              X