Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
Morning all.
last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
Its always to build this separately to a string variable and write it...
Dim strData As String If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF If Me.ComboBox11.value < "" Then strData = strData & Me.ComboBox11.value & vbLF If Me.ComboBox12.value < "" Then strData = strData & Me.ComboBox12.value & vbLF If Me.ComboBox13.value < "" Then strData = strData & Me.ComboBox13.value & vbLF strData = Mid(strData,2) -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
vbLF in front...
If Me.ComboBox9.value < "" Then strData = vbLF & Me.ComboBox9.value If Me.ComboBox11.value < "" Then strData = vbLF & strData & Me.ComboBox11.value If Me.ComboBox12.value < "" Then strData = vbLF & strData & Me.ComboBox12.value If Me.ComboBox13.value < "" Then strData = vbLF & strData & Me.ComboBox13.value strData = Mid(strData,2) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Its always to build this separately to a string variable and write it... Dim strData As String If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF If Me.ComboBox11.value < "" Then strData = strData & Me.ComboBox11.value & vbLF If Me.ComboBox12.value < "" Then strData = strData & Me.ComboBox12.value & vbLF If Me.ComboBox13.value < "" Then strData = strData & Me.ComboBox13.value & vbLF strData = Mid(strData,2) -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
Hi Jacob,
Thank you for your response. All looks good, except that the first option doesn't enter a value. If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF It seems to me that the iif(me.combobox9.value="","",me.combobox9.value & chr(10)) does the same thing. What am I missing here? Again, thank you. "Jacob Skaria" wrote: Its always to build this separately to a string variable and write it... Dim strData As String If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF If Me.ComboBox11.value < "" Then strData = strData & Me.ComboBox11.value & vbLF If Me.ComboBox12.value < "" Then strData = strData & Me.ComboBox12.value & vbLF If Me.ComboBox13.value < "" Then strData = strData & Me.ComboBox13.value & vbLF strData = Mid(strData,2) -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
Steve
How about this... Dim strData As String strData = Trim(Me.ComboBox1.Value) & Chr(10) & _ Trim(Me.ComboBox2.Value) & Chr(10) & _ Trim(Me.ComboBox3.Value) & Chr(10) & _ Trim(Me.ComboBox4.Value) strData = Replace(strData, String(3, Chr(10)), Chr(10)) ActiveCell.Value = Replace(strData, String(2, Chr(10)), Chr(10)) Again; there was an erro in my first posting; hope you have tried the corrected one.. -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Hi Jacob, Thank you for your response. All looks good, except that the first option doesn't enter a value. If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF It seems to me that the iif(me.combobox9.value="","",me.combobox9.value & chr(10)) does the same thing. What am I missing here? Again, thank you. "Jacob Skaria" wrote: Its always to build this separately to a string variable and write it... Dim strData As String If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF If Me.ComboBox11.value < "" Then strData = strData & Me.ComboBox11.value & vbLF If Me.ComboBox12.value < "" Then strData = strData & Me.ComboBox12.value & vbLF If Me.ComboBox13.value < "" Then strData = strData & Me.ComboBox13.value & vbLF strData = Mid(strData,2) -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
Ok, well, I'm probably not understanding my own post. Each variation does the
same things. I'll consider this solved, and go back and work through it to see what I'm not able to view clearly now. Thank you for your helps. They're deeply appreciated. Best. "Jacob Skaria" wrote: Steve How about this... Dim strData As String strData = Trim(Me.ComboBox1.Value) & Chr(10) & _ Trim(Me.ComboBox2.Value) & Chr(10) & _ Trim(Me.ComboBox3.Value) & Chr(10) & _ Trim(Me.ComboBox4.Value) strData = Replace(strData, String(3, Chr(10)), Chr(10)) ActiveCell.Value = Replace(strData, String(2, Chr(10)), Chr(10)) Again; there was an erro in my first posting; hope you have tried the corrected one.. -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Hi Jacob, Thank you for your response. All looks good, except that the first option doesn't enter a value. If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF It seems to me that the iif(me.combobox9.value="","",me.combobox9.value & chr(10)) does the same thing. What am I missing here? Again, thank you. "Jacob Skaria" wrote: Its always to build this separately to a string variable and write it... Dim strData As String If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF If Me.ComboBox11.value < "" Then strData = strData & Me.ComboBox11.value & vbLF If Me.ComboBox12.value < "" Then strData = strData & Me.ComboBox12.value & vbLF If Me.ComboBox13.value < "" Then strData = strData & Me.ComboBox13.value & vbLF strData = Mid(strData,2) -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
combobox, and chr(10)
Hi JAcob,
I did indeed try your correction. I'll try this and see how it works. "Jacob Skaria" wrote: Steve How about this... Dim strData As String strData = Trim(Me.ComboBox1.Value) & Chr(10) & _ Trim(Me.ComboBox2.Value) & Chr(10) & _ Trim(Me.ComboBox3.Value) & Chr(10) & _ Trim(Me.ComboBox4.Value) strData = Replace(strData, String(3, Chr(10)), Chr(10)) ActiveCell.Value = Replace(strData, String(2, Chr(10)), Chr(10)) Again; there was an erro in my first posting; hope you have tried the corrected one.. -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Hi Jacob, Thank you for your response. All looks good, except that the first option doesn't enter a value. If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF It seems to me that the iif(me.combobox9.value="","",me.combobox9.value & chr(10)) does the same thing. What am I missing here? Again, thank you. "Jacob Skaria" wrote: Its always to build this separately to a string variable and write it... Dim strData As String If Me.ComboBox9.value < "" Then strData = Me.ComboBox9.value & vbLF If Me.ComboBox11.value < "" Then strData = strData & Me.ComboBox11.value & vbLF If Me.ComboBox12.value < "" Then strData = strData & Me.ComboBox12.value & vbLF If Me.ComboBox13.value < "" Then strData = strData & Me.ComboBox13.value & vbLF strData = Mid(strData,2) -- If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. last week-- the 4th, I'd posted about this and while I received a response it didn't actually solve my issue. Below is my initial post from the 4th. (this is rather extended-- so please forgive me up front. I'm trying to avoid repeating what I've done already) ---------------------------------------- Howdie all. I've made a user form, and set it up with some combo boxes. Presently, my equations are asking for the input for 4 comboboxes. If a combobox is empty, it still inputs a blank value. I'd like to set it up so that if a value is not chosen for a combo box, it leaves that blank value out of the final input. Presently my equation reads: ActiveCell.value = Me.ComboBox1.value & Chr(10) & Me.ComboBox2.value & Chr(10) & Me.ComboBox3.value & Chr(10) & Me.ComboBox4.value For example, what I've got is combobox1 has a value, and then combobox2 and 3 are blank, then combobox4 has a value. Instead of placing two blanks in the input, I'd like it to only have the values from 1 and 4 input. I.e., Instead of A D I'd like A D How would I accomplish that? Thank you in advance for your helps. Best. -------------------------------------------- Here is what I wound up using, and it still doesn't quite do what I'm looking to accomplish. Definitely closer, but not exactly it. ============= IIf(Me.ComboBox9.value = "", "", Me.ComboBox9.value) & _ IIf(Me.ComboBox11.value = "", "", Chr(10) & Chr(10) & Me.ComboBox11.value) & _ IIf(Me.ComboBox12.value = "", "", Chr(10) & Chr(10) & Me.ComboBox12.value) & _ IIf(Me.ComboBox13.value = "", "", Chr(10) & Chr(10) & Me.ComboBox13.value) or IIf(Me.ComboBox10.value = "", "", Me.ComboBox10.value) & _ IIf(Me.ComboBox15.value = "", "", Chr(10) & Chr(10) & Me.ComboBox15.value) ===================== Presently, what I'm getting is: A B which, while I want this, in other uses where I have four IIf's, I get A B C D when I want A B C D Thus, my goal is that when I only have one truepart I want A when I have two truepart, I want A B or A C or A D And if I have 3 truepart for iif I want A B C or A B D or A C D However, what I get with the present configuration I'm getting A B C D I again apologize for this being so protracted a post. I hope that it's clear. If not, please ask me questions to help clarify. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Combobox options based on the input of another combobox | Excel Programming | |||
ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming |