Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 36

Thread: Combobox's

  1. #1
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location

    Combobox's

    Hey I am new in here...

    i need help with this Function..

    Combobox1 have Value 0-7 where "0" nothing happend on userform

    when Combobox1 is "1" then Textbox1 set value in cell "D3" and Textbox2 set value in Cell "E3" and Textbox3 set Value in cell "F3"
    when Combobox1 is "2" then Textbox1 set value in cell "G3" and Textbox2 set value in Cell "H3" and Textbox3 set Value in cell "I3"
    when Combobox1 is "3" then Textbox1 set value in cell "J3" and Textbox2 set value in Cell "K3" and Textbox3 set Value in cell "L3"
    when Combobox1 is "4" then Textbox1 set value in cell "M3" and Textbox2 set value in Cell "N3" and Textbox3 set Value in cell "O3"
    when Combobox1 is "5" then Textbox1 set value in cell "P3" and Textbox2 set value in Cell "Q3" and Textbox3 set Value in cell "R3"
    when Combobox1 is "6" then Textbox1 set value in cell "S3" and Textbox2 set value in Cell "T3" and Textbox3 set Value in cell "U3"
    when Combobox1 is "7" then Textbox1 set value in cell "V3" and Textbox2 set value in Cell "W3" and Textbox3 set Value in cell "Z3"

    I have look around The Google and Couldn Figure it out

    The code i got at this moment looks like this
    Private Sub UserForm_Initialize()   
    Dim i As Long
    Dim f As Long
    For i = 1 To 7
    Me.ComboBox2.AddItem "" & i
    ComboBox2.List = Sheets("H-erne").Range("C3:C131").Value
    Next i
    For f = 0 To 7
    Me.ComboBox1.AddItem "" & f
    Next f
    Label6.Caption = Sheets("H-erne").Range("Y3").Value
    Label7.Caption = Sheets("H-erne").Range("Z3").Value
    Label8.Caption = Sheets("H-erne").Range("AA3").Value
    End Sub
    
    
    Private Sub CommandButton1_Click()
    UserForm4.Label6.Visible = True
    UserForm4.Label7.Visible = True
    UserForm4.Label8.Visible = True
    Label6.Caption = Sheets("H-erne").Range("Y3").Value
    Label7.Caption = Sheets("H-erne").Range("Z3").Value
    Label8.Caption = Sheets("H-erne").Range("AA3").Value
    End Sub
    
    Private Sub CommandButton2_Click()
    UserForm4.Label6.Visible = False
    UserForm4.Label7.Visible = False
    UserForm4.Label8.Visible = False
    End Sub
    
    Private Sub CommandButton3_Click()
    UserForm4.Hide
    End Sub
    
    Private Sub TextBox1_Change()
    Sheets("H-erne").Range("D3") = TextBox1.Value
    End Sub
    
    Private Sub TextBox2_Change()
    Sheets("H-erne").Range("E3") = TextBox2.Value
    End Sub
    
    Private Sub TextBox3_Change()
    Sheets("H-erne").Range("F3") = TextBox3.Value
    End Sub
    that's what i haven figure out at this moment

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Welcome to the forum! When you get 5-10 posts, you will be able to post links and attach files.

    Private Sub ComboBox1_Change()    
        Dim r As Range, i As Integer
        Set r = Range("D3")
        Select Case ComboBox1
            Case 1 To 7
                Set r = r.Offset(, ComboBox1 * 3 - 3)
                For i = 1 To 3
                    Controls("TextBox" & i) = r.Offset(, i - 1)
                Next i
                If ComboBox1 = 7 Then TextBox3 = Range("Z3")
            Case Else
        End Select
    End Sub

  3. #3
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    I have now look't at the Code but couldn Figure out where to Put it into my code
    i have Reset my old code with this code and it aint work

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Put it in the userform object.

  5. #5
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    i did But it aint Work

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    "Aint Work" does not help me help you. That means:
    1. Nothing happened
    2. Error occurred, and if so where
    3. Not what was needed.
    4. etc.

    I suspect that your other code is the issue. Copy the userform and use only my code in the userform. Or, make a new form with combobox1, textbox 1-3, and command button. I made an example file for you that "works".
    Attached Files Attached Files

  7. #7
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    Hm i dont think its right
    Because in My textbox i should Could set Value by Enter it into the Specific Cell's

    and in your File I can se you have Preset it with some kind of Formula "there are No formula" in my cell's

    if i may i could put a Link from my Dropbox with the file

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    When you get 5 posts, you can attach a file. Click Go Advanced button in lower right of a reply. Then click Manage Attachments link below reply box. Browse and Upload the simple obfuscated file if needed.

    Formula, manual data, a cell value is a cell value. Simple files isolate problems. When a whole project is added, nailing down what other problems may take more time that solving the problem asked.

  9. #9
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    Yes i Know a Cell Value is what you Put Into it How ever When i going to use My file Form Start There Will Be Nothing in those Cell's

    and off course it take time to make a Solution of what other might have been trying to Solve by any kind

    it's on Userform4

    And No Password on File
    Attached Files Attached Files

  10. #10
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Before I look at your file, you may want to look at this method.

    It is similar but sort of reversed. It links the textboxes to the cells based on combobox which changes the controlsource property value. So, change one, you change the other. I showed userform modeless so one could test the two-way link. I also added some labels to make the links more clear.

    The case else is not in the file. I just added it.

    Private Sub ComboBox1_Change()    
        Dim r As Range, i As Integer
        Set r = Range("D3")
        Select Case ComboBox1
            Case 1 To 7
                Set r = r.Offset(, ComboBox1 * 3 - 3)
                For i = 1 To 3
                    Controls("TextBox" & i).ControlSource = r.Offset(, i - 1).Address
                    Controls("Label" & i).Caption = r.Offset(, i - 1).Address(False, False)
                Next i
                If ComboBox1 = 7 Then
                    TextBox3.ControlSource = Range("Z3").Address
                    Label3.ControlSource = Range("Z3").Address(False, False)
                End If
            Case Else
                For i = 1 To 3
                    Controls("TextBox" & i).ControlSource = ""
                    Controls("Label" & i).Caption = "No Link"
                Next i
        End Select
    End Sub
    Attached Files Attached Files

  11. #11
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    It's look like it's Work..

    i try to put it into my File

    Thanks Alot

  12. #12
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    I am sorry But i Cant get it to work as i Want it

    when i put your code in It set Value in the Wrong Labels
    Last edited by Hjemmet; 09-08-2019 at 02:06 PM.

  13. #13
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    Post the code that you tried based on mine. Obviously, your userform3 has different controls and control names than mine does.

    Both of my examples fills textbox controls. Your userform4 has no textbox controls. My last example linked the cells and textbox controls as detailed in your post #1. It also updated the 3 label controls above the textbox controls. That is all to show you how it is done. It is up to you to change it to suit your needs.

    It can get confusing when one sticks with the generic control names. I did i=1 to 3. If you don't change control names, you need to do i=3 to 5 for labels and i=6 to 8 for the bottom labels. That can be done in one loop. If you stick with labels, the cell values are not linked by the ControlSource property since that is for textbox controls. If you don't want the user to change a textbox value, set property Enabled=False. One can use nonsensical control names or names out of suffix order. It makes coding more involved though. In those cases, one can make use of control arrays.

    If you stick with the label controls for the cell values, then the Caption property would get the cell's value property. e.g.
    j = j+1 
    Controls("Label" & i).Caption = r.Offset(, j - 1)

  14. #14
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    I give it a try to morrow its Quit late in Denmark now 23:54
    for the moment i make a New Userform5 in my file Only with you code for combobox's
    and make pictures of every step i do with Combobox so you could se What happen

    ON the userform i have a Command Button where i could Change Value "Ændre Resultat" when click on that Label6-7-8 get Invisibel and
    textbox1-2-3 visibel and now value can be set
    then click on Command Button "Gem Resultat" save Result and Textbox is Hide when Labels now is Visibel and show the Value of overall Result
    Last edited by Hjemmet; 09-08-2019 at 03:08 PM.

  15. #15
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    I am not sure that pictures will help me. Sometimes, a before and after screen shot is useful. That is usually posted in an attachment. That can work well for worksheet data. Usually, a copy of the worksheet before and after a manual markup is better than a picture.

    I know what happens by code. I don't know words in a language other than English. Obviously, you are better at English than my any other language.

    Code alone is not enough in your case as your userform did not have the control types and control names that were in post #1. That is why whatever you tried does not "work" I suspect. My code "works" as shown in my code and the file. If you change control names, control types and such, then the goals using controls detailed in post #1 and my examples changed.

    You need to decide what your goals are. If you are again changing control types, control names or such in userform5, then the solution must be changed accordingly. If you like, just post a simple file with the userform5 and the sheet that it needs.

    For the bigger projects, I find that prefix or suffix control names make it easier to understand when I later modify it again. For example. tbC1_1, tbC1_2, tbC1_3, lbC1_1, lbC1_2, lbC1_3. So, tb is short for TextBox, C1 is short for Category1. It is rather obvious which label describes what textbox using this concept. For your project, I can see what is what in your userform. I know how to make the controls loop even if not in a consistent order so don't worry about that right now.

  16. #16
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    Hm i dont know what i have done but some how it works
    But it seems there are a little minor problem

    It aint work when Combobox is 6 and 7
    and every change when combobox is 2-3-4-5
    it make change in the first 3 cell’s D3,E3,F3
    And this is for combobox1

  17. #17
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    well at Home after Job i Try to go trough my Code again to see what happen with fresh memory, and It work so far......
    The only Mistake i Find is no matter what the Combobox value is from "0" to "7" and Enter the Value in My textbox's then the first 3 cell's Chang value
    and that is not what it should do........

  18. #18
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    New File with Your Code On userform5 with English name on Buttons and Labels....
    Attached Files Attached Files

  19. #19
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    What sheet is the linking supposed to be? Normally, one plays a macro from the activesheet that the userform interacts with. If I am not told what sheet, I have to assume one.

    Since the linking happens, you should remove the textbox change events unless you need more than link updates.

    As a troubleshooting tool since you did not do the labels like I did, you can use this to doubleclick and see the active linked cell address.

    Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)    
        MsgBox TextBox1.ControlSource
    End Sub
    If you want my code to link to cells other than an activesheet, you need to set it. Here are what has to be changed.
    Private Sub ComboBox1_Change()    
        Dim r As Range, i As Integer
        Set r = Worksheets("H-erne").Range("D3")
        Select Case ComboBox1
            Case 1 To 7
                Set r = r.Offset(, ComboBox1 * 3 - 3)
                For i = 1 To 3
                Controls("TextBox" & i).Visible = True
                    Controls("TextBox" & i).ControlSource = r.Offset(, i - 1).Address(external:=True)
                    'Controls("Label" & i).Caption = r.Offset(, i - 1).Address(False, False)
                Next i
                If ComboBox1 = 7 Then
                    TextBox3.ControlSource = Range("X3").Address(external:=True)
                    'Label3.ControlSource = Range("X3").Address(False, False)
                End If
            Case Else
        End Select
    End Sub
    Last edited by Kenneth Hobs; 09-09-2019 at 10:23 AM.

  20. #20
    VBAX Regular
    Joined
    Sep 2019
    Posts
    20
    Location
    I am sorry i didn say what Sheets userform should work on
    And yes it’s sheets(H-erne)

    i have put your Options (red) in my code and it’s okay
    but i still get some mistake when i run Code and userform appear
    Click command button input value’s in Textbox’s
    works fine for combobox value 1

    thats what happen Combobox (CB) textbox (TB)1 to 3,

    If CB is “0” then value in (TB) 1-3 set value in Cell D3,E3,F3 that’s wrong
    If CB is “1” then value in (TB) 1-3 set value in cell D3,E3,F3 that’s Right.
    If CB is “2” then value in (TB) 1-3 set value in Cell D3,E3,F3 and F3,G3,H3 thats Wrong
    Its should only be in F3,G3,H3
    If CB is “3” then value in (TB) 1-3 set value in cell D3,E3,F3 and I3,J3,K3 thats Wrong
    it’s should only be on I3,J3,K3
    If CB os “4” then value in (TB) 1-3 set value in Cell D3,E3,F3 and L3,M3,N3 thats Wrong
    it’s should only be on L3,M3,N3
    If CB os “5” then value in ((TB) 1-3 set value in Cell D3,E3,F3 and O3,P3,Q3 thats Wrong
    it’s should only be on O3,P3,Q3
    If CB os “6” then value in (TB) 1-3 set value in Cell D3,E3,F3 and R3,S3,T3 thats Wrong
    it’s should only be on R3,S3,T3
    If CB os “7” then value in (TB) 1-3 set value in Cell D3,E3,F3 and U3,V3,W3 thats Wrong
    it’s should only be on U3,V3,W3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •