Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 40 of 40

Thread: Combobox's

  1. #21
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,902
    Location
    You can easily test code using Debug.print. It puts a run's result into Immediate Window. IF not shown, set it in View menu.

    I did not see that you had removed the Case Else condition when I posted the revised code in #19. I also modified that case check since 1 to 7 would allow 1.1, 1.56 and such. I rename the file to Ken.xlsm to make this more clear. Here it is:
    0
    1 '[ken.xlsm]H-erne'!$D$3 '[ken.xlsm]H-erne'!$E$3 '[ken.xlsm]H-erne'!$F$3
    2 '[ken.xlsm]H-erne'!$G$3 '[ken.xlsm]H-erne'!$H$3 '[ken.xlsm]H-erne'!$I$3
    3 '[ken.xlsm]H-erne'!$J$3 '[ken.xlsm]H-erne'!$K$3 '[ken.xlsm]H-erne'!$L$3
    4 '[ken.xlsm]H-erne'!$M$3 '[ken.xlsm]H-erne'!$N$3 '[ken.xlsm]H-erne'!$O$3
    5 '[ken.xlsm]H-erne'!$P$3 '[ken.xlsm]H-erne'!$Q$3 '[ken.xlsm]H-erne'!$R$3
    6 '[ken.xlsm]H-erne'!$S$3 '[ken.xlsm]H-erne'!$T$3 '[ken.xlsm]H-erne'!$U$3
    7 '[ken.xlsm]H-erne'!$V$3 '[ken.xlsm]H-erne'!$W$3 '[ken.xlsm]H-erne'!$X$3
    That looks like what you wanted in #1 except for X3. I fixed that too in the code below. Why was it "wrong", it wasn't. See your code:
    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
    Private Sub ComboBox1_Change()    
        Dim r As Range, i As Integer
        Set r = Worksheets("H-erne").Range("D3")
        Select Case ComboBox1
            Case 1, 2, 3, 4, 5, 6, 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("Z3").Address(external:=True)
                    '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
         'Debug.Print ComboBox1, TextBox1.ControlSource, TextBox2.ControlSource, TextBox3.ControlSource
    End Sub
    Last edited by Kenneth Hobs; 09-10-2019 at 06:54 PM.

  2. #22
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    In Userform5, all you need is:

    Dim sn
    Private Sub UserForm_Initialize()
        ComboBox1.List = [row(1:7)]
        ComboBox2.List = Ark1.Columns(3).SpecialCells(-4123).Value
        sn = Ark1.Cells(1, 3).CurrentRegion
    End Sub
    
    Private Sub ComboBox1_Change()
       If ComboBox1.ListIndex = -1 Or ComboBox2.ListIndex = -1 Then Exit Sub
    
       For j = 1 To 3
          Me("TextBox" & j) = sn(3 + ComboBox2.ListIndex, j + 2 + 3 * ComboBox1.ListIndex)
       Next
    End Sub
    Remove the labels (i.e. label6) that block the textboxes.

    First: choose the player in combobox2
    Second: choose the round in Combobox1

    NB.
    - Do not use merged cells
    - Do not hide columns
    - Always start in Cell A1: use row 1 and column A
    - reduce the interaction with the worksheet (reading/writing) to a minimum
    Attached Files Attached Files
    Last edited by snb; 09-11-2019 at 01:12 AM.

  3. #23
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    i have try your Code and File (snb) Nothing Happend when Typeing value in Textbox's


    Kenneth Hobs Could you upload a File Please i DONT know where to put
    0
    1 '[ken.xlsm]H-erne'!$D$3 '[ken.xlsm]H-erne'!$E$3 '[ken.xlsm]H-erne'!$F$3
    2 '[ken.xlsm]H-erne'!$G$3 '[ken.xlsm]H-erne'!$H$3 '[ken.xlsm]H-erne'!$I$3
    3 '[ken.xlsm]H-erne'!$J$3 '[ken.xlsm]H-erne'!$K$3 '[ken.xlsm]H-erne'!$L$3
    4 '[ken.xlsm]H-erne'!$M$3 '[ken.xlsm]H-erne'!$N$3 '[ken.xlsm]H-erne'!$O$3
    5 '[ken.xlsm]H-erne'!$P$3 '[ken.xlsm]H-erne'!$Q$3 '[ken.xlsm]H-erne'!$R$3
    6 '[ken.xlsm]H-erne'!$S$3 '[ken.xlsm]H-erne'!$T$3 '[ken.xlsm]H-erne'!$U$3
    7 '[ken.xlsm]H-erne'!$V$3 '[ken.xlsm]H-erne'!$W$3 '[ken.xlsm]H-erne'!$X$3
    Last edited by Hjemmet; 09-11-2019 at 02:19 AM.

  4. #24
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    - reduce the interaction with the worksheet (reading/writing) to a minimum

    In the file the changes will be written into the worksheet as soon as the Userform will be closed. (Event QueryClose).
    Until then the changes will be stored in Combobox2.
    Attached Files Attached Files

  5. #25
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    HM HM HM HM (snb) that works Just like what i want it to Do.....

    Both Combobox work's
    But The Last 3 Colomn where i have "formulaes to take Effect after what Result i have Set "
    Colomn W Should have Formula from Colomn = (B+E+H+K+N+Q+T) Numbers of how many "180" there was scored
    Colomn X Should Have Formula from Colomn = Max (C;F;I;L;O;R;U) Tells Which is the Highest Close in Game
    Colomn Y should Have Formula from Colomn = Min (D;G;J;M;P;S;V) Tells Which is the Lowest NUmbers of Dart's To end Legs in a game.....

    Those Value was used by my Label 6-7-8 And them i still Want......

  6. #26
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    I'd prefer:
    Attached Files Attached Files

  7. #27
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    Kenneth Hobs Is it possibel you could send the file,where you have made your change, i could not figure out where to put the code with Filecontrol
    you have put in #21



  8. #28
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    Somewhat simpler
    Attached Files Attached Files
    Last edited by snb; 09-12-2019 at 04:38 AM.

  9. #29
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    HM now we are near to get the Goal of what i want...

    But is it not Possibel to get The Value there is appear on frame's into Labels i dont like it as it looks like
    And a commanbutton to Accept Result before close Userform so i dont have to close Userform and start it again to result......

    But otherwise it Works

  10. #30
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    Those wishes are redundant.

  11. #31
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,902
    Location
    For #21, you just had to change the X3 to Z3 or replace the whole sub and delete your 3 Subs that were making textbox changes too as commented in red. That is pretty simple. Those small changes are in this file FWIW.

    The quote box showed results from the Debug.Print line for each combobox item selected for testing purposes.
    Attached Files Attached Files

  12. #32
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    Kenneth Hobs

    Thank you for Your Patience and Help it works just what i like it to do

    now i can Go further with the Next Level Chose Players
    --------------------------------------------------------------------
    Thank's for you Help Too "snb" Your idea works ok, But Still i dont like the Frame solution

  13. #33
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    That isn't the essence of my approach.
    Analyse the code, that's important.

  14. #34
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    I have now try your Code again "snb" and i got a minor problem when Chose Player and Cup game
    and put value in all textbox then only the first and Second Value in Frame Appears not the last one
    I have been trying to change Colomn Value in your code but nothing Help

    and also no value in Colomn"Y" is Empty even when i entered Value on Userform....




  15. #35
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    I have now try your Code again "snb" and i got a minor problem when Chose Player and Cup game
    and put value in all textbox then only the first and Second Value in Frame Appears not the last one
    I have been trying to change Colomn Value in your code but nothing Help

    and also no value in Colomn"Y" is Empty even when i entered Value on Userform....




    HM HM HM i dont know why but it seems that i have made this question twice
    Last edited by Hjemmet; 09-15-2019 at 05:35 AM.

  16. #36
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    to "snb"
    More trouble with Userform ......

    after entering value for Player 1 and 2

    and going to another player Like Player 14 and Cupgame 1 then there was Value in Textbox box even When i NOT have set any value...
    and still no value in Colomn "Y" on sheet's

  17. #37
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    First Picture i Double click on The Cell for wich Player the Result is needed...

    Second picture when Double Click again then result there has Been Set

    Third picture here you can see The value aint Appear for result from Colomn "Y"
    and it is the same for each Player
    Attached Images Attached Images
    • File Type: jpg 1.jpg (11.8 KB, 3 views)
    Attached Files Attached Files

  18. #38
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    In this forum we only give suggestions how you can attain your goal: we help developers.
    If you need a turnkey solution this is the wrong place to be.
    But to be honest: you practically got one.

  19. #39
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    Your right i hot Help with my first issue
    but it aint work as it should
    secondly i couldn see any place were this forum was for
    Developers

    so im sorry if i have ask again for something that aint work

  20. #40
    VBAX Regular
    Joined
    Sep 2019
    Posts
    31
    Location
    well After i came to my Computer then i have readen the "FAQ" and found this so its Not just only developers there Get Help in here
    but Still Thanks Anyway

    youre Regards from me

    Should I become a member?Anyone who wants to improve their efficiency, or someone else's efficiency, in using the applications that support VBA, such as Microsoft Office.
    
    So you're no coder? That's okay! That's why we're here! Have you ever wished you could understand the code you've seen posted? Or wished you knew what to do with that code? That's our goal! We not only give you the code, we tell you exactly how to use it. And if you still have difficulty, with our code or your own, you can get free help!
    
    If you don't want to code it yourself, you can post your project in our Job Postings forum!
    
    If you are a coder, and want to help, we insist only that you treat the askers with dignity and respect. Condescension and egotistical attitudes will not be tolerated, and are considered grounds for suspension of membership.
    
    
    
    You must become a member to see all of the forums we have available.

Posting Permissions

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