Combobox's

[Hjemmet]

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

[Kenneth Hobs]

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

[Hjemmet]

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

[Kenneth Hobs]

Put it in the userform object.

[Hjemmet]

i did But it aint Work

[Kenneth Hobs]

"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".

[Hjemmet]

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

[Kenneth Hobs]

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.

[Hjemmet]

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

[Kenneth Hobs]

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

[Hjemmet]

It's look like it's Work..

i try to put it into my File

Thanks Alot

[Hjemmet]

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

[Kenneth Hobs]

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)

[Hjemmet]

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

[Kenneth Hobs]

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.

[Hjemmet]

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

[Hjemmet]

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........

[Hjemmet]

New File with Your Code On userform5 with English name on Buttons and Labels....

[Kenneth Hobs]

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

[Hjemmet]

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