Consulting

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

Thread: Combobox's

  1. #21
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,847
    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,519
    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
    19
    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,519
    - 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
    19
    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,519
    I'd prefer:
    Attached Files Attached Files

  7. #27
    VBAX Regular
    Joined
    Sep 2019
    Posts
    19
    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,519
    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
    19
    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,519
    Those wishes are redundant.

  11. #31
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,847
    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
    19
    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,519
    That isn't the essence of my approach.
    Analyse the code, that's important.

  14. #34
    VBAX Regular
    Joined
    Sep 2019
    Posts
    19
    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
    19
    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.

Posting Permissions

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