PDA

View Full Version : Combobox's



Hjemmet
09-07-2019, 02:20 PM
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
09-07-2019, 05:14 PM
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
09-08-2019, 02:22 AM
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
09-08-2019, 06:37 AM
Put it in the userform object.

Hjemmet
09-08-2019, 06:41 AM
i did But it aint Work

Kenneth Hobs
09-08-2019, 08:48 AM
"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
09-08-2019, 09:05 AM
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
09-08-2019, 09:18 AM
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
09-08-2019, 09:31 AM
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
09-08-2019, 09:53 AM
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
09-08-2019, 09:57 AM
It's look like it's Work..

i try to put it into my File

Thanks Alot

Hjemmet
09-08-2019, 01:24 PM
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
09-08-2019, 02:41 PM
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
09-08-2019, 02:56 PM
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
09-08-2019, 06:02 PM
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
09-08-2019, 11:35 PM
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
09-09-2019, 05:57 AM
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
09-09-2019, 08:23 AM
New File with Your Code On userform5 with English name on Buttons and Labels....

Kenneth Hobs
09-09-2019, 10:10 AM
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
09-10-2019, 03:22 AM
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

Kenneth Hobs
09-10-2019, 06:37 PM
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

snb
09-11-2019, 12:28 AM
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

Hjemmet
09-11-2019, 02:08 AM
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

snb
09-11-2019, 11:26 AM
- 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.

Hjemmet
09-11-2019, 11:49 AM
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......

snb
09-11-2019, 02:44 PM
I'd prefer:

Hjemmet
09-12-2019, 12:04 AM
Kenneth Hobs (http://www.vbaexpress.com/forum/member.php?3661-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

snb
09-12-2019, 12:37 AM
Somewhat simpler

Hjemmet
09-12-2019, 12:51 AM
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

snb
09-12-2019, 04:41 AM
Those wishes are redundant.

Kenneth Hobs
09-12-2019, 07:08 AM
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.

Hjemmet
09-12-2019, 10:10 PM
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

snb
09-13-2019, 12:06 AM
That isn't the essence of my approach.
Analyse the code, that's important.

Hjemmet
09-15-2019, 05:10 AM
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....



:banghead:

Hjemmet
09-15-2019, 05:12 AM
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....


http://www.vbaexpress.com/forum/images/smilies/banghead.gif

HM HM HM i dont know why but it seems that i have made this question twice

Hjemmet
09-20-2019, 03:55 AM
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

Hjemmet
12-08-2019, 03:04 PM
25579

First Picture i Double click on The Cell for wich Player the Result is needed...


25580

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


25581

Third picture here you can see The value aint Appear for result from Colomn "Y"
and it is the same for each Player

snb
12-09-2019, 01:10 AM
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.

Hjemmet
12-09-2019, 03:09 AM
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

Hjemmet
12-09-2019, 09:12 AM
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.