Consulting

Results 1 to 20 of 40

Thread: Combobox's

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #21
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    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.

Posting Permissions

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