Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read List1 and write List2
This is a 2 part question.
1. I create 2 lists, List1 on column A and List2 in column B. List1 and List2 are linked to a Forms ComboBox and can be switch from one list to the other via 2 OptionButton. List1 is the narrative of a job and List2 is the acronym/code for the narrative. What I do is look at the narrative and then click on one of OptionButtons to switch to the acronym and click on it to trigger a macro to paste that/ code to a preselected cell. This works fine, but I would like to change it to something more stream line, like I see the Narrative(from List1) on the combobox and when I click/choose one of the values it narrative it writes the code. ie; I select the value "Photograph difficult matter" and it writes PDM on a selected cell. I had some ideas(Vlookup) but I was not able to make it work Any guidance will be appreciated 2.When I created the Forms combobox and the I change form List1 to Liist2 via clicking on OptionButtons the combobox will automatically refresh to the respective cell, meaning the value of A2 would change to the value of B2 by switching Option Boxes. The question is, when I create a Control Combobox It Will not refresh automatically, is there something I can do to autorefresh. Thank you very much for your help Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read List1 and write List2
An approach to consider, it sounds like you are using each list separately
to fill the combo box. ? Why not use both together setting the number of columns (ColumnCount) to 2, you can control which column is displayed by setting the ColumnWidth either as 0,100 or 100,0 this will show either column. (choose an appropriate value for the column to be displayed, anything 0 will show the column). Your option button would change the value of the ColumnWidth property, hence change from column A to column B. you might find two buttons one to select column A (Text) and obe for column B (Acronym) is the easiest way of setting the combobox to show what you want in the list. Set Combobox1.RowSource to equal the range of both columns A and B containing the data list eg RowSource = Sheet1!A1:B4 ' show column A Private Sub CommandButton1_Click() ComboBox1.ColumnWidths = "100,0" End Sub ' show column B Private Sub CommandButton2_Click() ComboBox1.ColumnWidths = "0,100" End Sub Your selection shown in the combobox will change as the columns are switched I think this will solve both your questions? Cheers Nigel "Kevin" ! wrote in message ... This is a 2 part question. 1. I create 2 lists, List1 on column A and List2 in column B. List1 and List2 are linked to a Forms ComboBox and can be switch from one list to the other via 2 OptionButton. List1 is the narrative of a job and List2 is the acronym/code for the narrative. What I do is look at the narrative and then click on one of OptionButtons to switch to the acronym and click on it to trigger a macro to paste that/ code to a preselected cell. This works fine, but I would like to change it to something more stream line, like I see the Narrative(from List1) on the combobox and when I click/choose one of the values it narrative it writes the code. ie; I select the value "Photograph difficult matter" and it writes PDM on a selected cell. I had some ideas(Vlookup) but I was not able to make it work Any guidance will be appreciated 2.When I created the Forms combobox and the I change form List1 to Liist2 via clicking on OptionButtons the combobox will automatically refresh to the respective cell, meaning the value of A2 would change to the value of B2 by switching Option Boxes. The question is, when I create a Control Combobox It Will not refresh automatically, is there something I can do to autorefresh. Thank you very much for your help Kevin ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read List1 and write List2
Nigel, thank you for your response, It took me this long to respond because
I was out of town. I have tried you advice and it works very well, Partially!. I had some trouble trying to figure out exactly what you where trying to say by setting the ColumnWidth either as 0,100 or 100,0, I think I got it to work. But I'm still having trouble trying to see one list and clicking on a value from column A and then writing what is in Column B (Acronym). I'm new at excel so is going to take me a bit longer than normal, I'll continue to study your response in hope that I can figure it out. Thank you. "Nigel" wrote in message ... An approach to consider, it sounds like you are using each list separately to fill the combo box. ? Why not use both together setting the number of columns (ColumnCount) to 2, you can control which column is displayed by setting the ColumnWidth either as 0,100 or 100,0 this will show either column. (choose an appropriate value for the column to be displayed, anything 0 will show the column). Your option button would change the value of the ColumnWidth property, hence change from column A to column B. you might find two buttons one to select column A (Text) and obe for column B (Acronym) is the easiest way of setting the combobox to show what you want in the list. Set Combobox1.RowSource to equal the range of both columns A and B containing the data list eg RowSource = Sheet1!A1:B4 ' show column A Private Sub CommandButton1_Click() ComboBox1.ColumnWidths = "100,0" End Sub ' show column B Private Sub CommandButton2_Click() ComboBox1.ColumnWidths = "0,100" End Sub Your selection shown in the combobox will change as the columns are switched I think this will solve both your questions? Cheers Nigel "Kevin" ! wrote in message ... This is a 2 part question. 1. I create 2 lists, List1 on column A and List2 in column B. List1 and List2 are linked to a Forms ComboBox and can be switch from one list to the other via 2 OptionButton. List1 is the narrative of a job and List2 is the acronym/code for the narrative. What I do is look at the narrative and then click on one of OptionButtons to switch to the acronym and click on it to trigger a macro to paste that/ code to a preselected cell. This works fine, but I would like to change it to something more stream line, like I see the Narrative(from List1) on the combobox and when I click/choose one of the values it narrative it writes the code. ie; I select the value "Photograph difficult matter" and it writes PDM on a selected cell. I had some ideas(Vlookup) but I was not able to make it work Any guidance will be appreciated 2.When I created the Forms combobox and the I change form List1 to Liist2 via clicking on OptionButtons the combobox will automatically refresh to the respective cell, meaning the value of A2 would change to the value of B2 by switching Option Boxes. The question is, when I create a Control Combobox It Will not refresh automatically, is there something I can do to autorefresh. Thank you very much for your help Kevin ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read List1 and write List2
Kevin,
Try putting the following code behind your form, I think it provides you the template you need. I have shown the value chosen in a MsgBox, you will use this value for your process. ' use double clicks a value in the combo box Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) With ComboBox1 If .ListIndex -1 Then 'this checks it is a valid list entry (not blank) MsgBox "Selection = " & Range("A1", "B4").Cells(.ListIndex + 1, 2) End If End With End Sub ' displays the name values columnA Private Sub CommandButton1_Click() ComboBox1.ColumnWidths = "100,0" End Sub 'displays the acronym values columnB Private Sub CommandButton2_Click() ComboBox1.ColumnWidths = "0,100" End Sub ' initialises the form showing just columnA Private Sub UserForm_Initialize() ComboBox1.ColumnWidths = "100,0" End Sub "Kevin" ! wrote in message ... Nigel, thank you for your response, It took me this long to respond because I was out of town. I have tried you advice and it works very well, Partially!. I had some trouble trying to figure out exactly what you where trying to say by setting the ColumnWidth either as 0,100 or 100,0, I think I got it to work. But I'm still having trouble trying to see one list and clicking on a value from column A and then writing what is in Column B (Acronym). I'm new at excel so is going to take me a bit longer than normal, I'll continue to study your response in hope that I can figure it out. Thank you. "Nigel" wrote in message ... An approach to consider, it sounds like you are using each list separately to fill the combo box. ? Why not use both together setting the number of columns (ColumnCount) to 2, you can control which column is displayed by setting the ColumnWidth either as 0,100 or 100,0 this will show either column. (choose an appropriate value for the column to be displayed, anything 0 will show the column). Your option button would change the value of the ColumnWidth property, hence change from column A to column B. you might find two buttons one to select column A (Text) and obe for column B (Acronym) is the easiest way of setting the combobox to show what you want in the list. Set Combobox1.RowSource to equal the range of both columns A and B containing the data list eg RowSource = Sheet1!A1:B4 ' show column A Private Sub CommandButton1_Click() ComboBox1.ColumnWidths = "100,0" End Sub ' show column B Private Sub CommandButton2_Click() ComboBox1.ColumnWidths = "0,100" End Sub Your selection shown in the combobox will change as the columns are switched I think this will solve both your questions? Cheers Nigel "Kevin" ! wrote in message ... This is a 2 part question. 1. I create 2 lists, List1 on column A and List2 in column B. List1 and List2 are linked to a Forms ComboBox and can be switch from one list to the other via 2 OptionButton. List1 is the narrative of a job and List2 is the acronym/code for the narrative. What I do is look at the narrative and then click on one of OptionButtons to switch to the acronym and click on it to trigger a macro to paste that/ code to a preselected cell. This works fine, but I would like to change it to something more stream line, like I see the Narrative(from List1) on the combobox and when I click/choose one of the values it narrative it writes the code. ie; I select the value "Photograph difficult matter" and it writes PDM on a selected cell. I had some ideas(Vlookup) but I was not able to make it work Any guidance will be appreciated 2.When I created the Forms combobox and the I change form List1 to Liist2 via clicking on OptionButtons the combobox will automatically refresh to the respective cell, meaning the value of A2 would change to the value of B2 by switching Option Boxes. The question is, when I create a Control Combobox It Will not refresh automatically, is there something I can do to autorefresh. Thank you very much for your help Kevin ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read List1 and write List2
Sorry Nigel, I been to busy and forgot to answer back.
Thank you for helping out with the code I tried it and it works as you said and I can tweak it to fit my needs. Thanks Kevin "Nigel" wrote in message ... Kevin, Try putting the following code behind your form, I think it provides you the template you need. I have shown the value chosen in a MsgBox, you will use this value for your process. ' use double clicks a value in the combo box Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) With ComboBox1 If .ListIndex -1 Then 'this checks it is a valid list entry (not blank) MsgBox "Selection = " & Range("A1", "B4").Cells(.ListIndex + 1, 2) End If End With End Sub ' displays the name values columnA Private Sub CommandButton1_Click() ComboBox1.ColumnWidths = "100,0" End Sub 'displays the acronym values columnB Private Sub CommandButton2_Click() ComboBox1.ColumnWidths = "0,100" End Sub ' initialises the form showing just columnA Private Sub UserForm_Initialize() ComboBox1.ColumnWidths = "100,0" End Sub "Kevin" ! wrote in message ... Nigel, thank you for your response, It took me this long to respond because I was out of town. I have tried you advice and it works very well, Partially!. I had some trouble trying to figure out exactly what you where trying to say by setting the ColumnWidth either as 0,100 or 100,0, I think I got it to work. But I'm still having trouble trying to see one list and clicking on a value from column A and then writing what is in Column B (Acronym). I'm new at excel so is going to take me a bit longer than normal, I'll continue to study your response in hope that I can figure it out. Thank you. "Nigel" wrote in message ... An approach to consider, it sounds like you are using each list separately to fill the combo box. ? Why not use both together setting the number of columns (ColumnCount) to 2, you can control which column is displayed by setting the ColumnWidth either as 0,100 or 100,0 this will show either column. (choose an appropriate value for the column to be displayed, anything 0 will show the column). Your option button would change the value of the ColumnWidth property, hence change from column A to column B. you might find two buttons one to select column A (Text) and obe for column B (Acronym) is the easiest way of setting the combobox to show what you want in the list. Set Combobox1.RowSource to equal the range of both columns A and B containing the data list eg RowSource = Sheet1!A1:B4 ' show column A Private Sub CommandButton1_Click() ComboBox1.ColumnWidths = "100,0" End Sub ' show column B Private Sub CommandButton2_Click() ComboBox1.ColumnWidths = "0,100" End Sub Your selection shown in the combobox will change as the columns are switched I think this will solve both your questions? Cheers Nigel "Kevin" ! wrote in message ... This is a 2 part question. 1. I create 2 lists, List1 on column A and List2 in column B. List1 and List2 are linked to a Forms ComboBox and can be switch from one list to the other via 2 OptionButton. List1 is the narrative of a job and List2 is the acronym/code for the narrative. What I do is look at the narrative and then click on one of OptionButtons to switch to the acronym and click on it to trigger a macro to paste that/ code to a preselected cell. This works fine, but I would like to change it to something more stream line, like I see the Narrative(from List1) on the combobox and when I click/choose one of the values it narrative it writes the code. ie; I select the value "Photograph difficult matter" and it writes PDM on a selected cell. I had some ideas(Vlookup) but I was not able to make it work Any guidance will be appreciated 2.When I created the Forms combobox and the I change form List1 to Liist2 via clicking on OptionButtons the combobox will automatically refresh to the respective cell, meaning the value of A2 would change to the value of B2 by switching Option Boxes. The question is, when I create a Control Combobox It Will not refresh automatically, is there something I can do to autorefresh. Thank you very much for your help Kevin ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
read only and write only | New Users to Excel | |||
I have a read only xl file, I need it to be read and write | Excel Discussion (Misc queries) | |||
I have a list1 and a list2, I want to remove list2 from list1 | Excel Worksheet Functions | |||
How can a file be converted from Read-Only to Read/Write | Excel Discussion (Misc queries) | |||
write to/read from add-ins | Excel Programming |