combobox, and chr(10)

S

Steve

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

Jacob Skaria

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)
 
S

Steve

Hi Sam,
I'd actually tried your version as well that day and it did the same thing
as the other guy who'd posted his response. I'd posted back to you but never
got any subsequent response. Please go back and look, and you'll see my
initial response to you.

Although, as I'd never seen his before, I spent more time with it, and found
why I kept getting the wrong "answer." It's still a bit short of my goal--
hence my post today.
 
J

Jacob Skaria

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
 
S

Steve

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

Jacob Skaria

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

Steve

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top