Compiling a sentence into an InputBox from various cells

[ilovelagar]

Hi everyone!

I'm creating an InputBox that pulls from various cells in my worksheet to create a sentence as the default value in the text-space. For example:

A1 = Policy Number
B1 = Credit Amount (i.e. $10)
C1 = $ Info [i.e. if credit is for one month (1 x 10), if two months (2 x 10)]
D1 = Date Policy Ended
E1 = Months Excluded from Bills

Thus, the sentence that the InputBox would display would be:
Today's Date + A1 + B1 + C1 + D1 + E1
Date - Policy # - Credit Amount - Info - Date Ended - Months Excluded

A text example would be:
30JUL2009 - 1234 - $10 - 1x10 - 01JUN2009 - Exc Jul,Aug09

The issue I'm having is how to have the InputBox macro ignore one of the cells if it's empty. My current code is:

Private Sub ContactNotes_Click()

Dim CNote

CNote = InputBox("", "Contact Note", Format(Date, "ddmmmyyyy") & " - " & Range("A1") & " - " & Range("B1") & " - " & Range("C1") & " - " & Range("D1") & " - " & Range("E1"))
        
End Sub

For these notes, I will not always have a credit amount, in which case the macro should ignore B1 and C1; the sentence would then be as follows:

30JUL2009 - 1234 - 01JUN2009 - Exc Jul,Aug09

Any ideas

Cheers,

- I Love Lagar -

[JBeaucaire]

Hi, Ilovelagar, welcome to the forum. You'll need to familiarize yourself with the forum rules.

#1 is an appropriate title, and yours is great!
#3 relates to CODE in your messages...you must put [code] and [/code] around your code.

If you'll edit that first post appropriately, I can offer a suggestion or two for you.

[ilovelagar]

Hi, Ilovelagar, welcome to the forum. You'll need to familiarize yourself with the forum rules.

#1 is an appropriate title, and yours is great!
#3 relates to CODE in your messages...you must put [code] and [/code] around your code.

If you'll edit that first post appropriately, I can offer a suggestion or two for you.

Apologies ... Fixed

[JBeaucaire]

I would simply test the cell B1 for a value, based on the answer TRUE/FALSE, you use two different concatenation formulas:

Private Sub ContactNotes_Click()
Dim CNote

If IsEmpty(Range("B1") Then
    CNote = InputBox("", "Contact Note", Format(Date, "ddmmmyyyy") & " - " & _
        Range("A1") & " - " & Range("D1") & " - " & Range("E1"))
Else
    CNote = InputBox("", "Contact Note", Format(Date, "ddmmmyyyy") & " - " & _
        Range("A1") & " - " & Range("B1") & " - " & Range("C1") & " - " & Range("D1") & " - " & Range("E1"))
End If    
End Sub

[ilovelagar]

Thanks so much, it worked!

I do have one issue though, when the macro pulls from the cells, the formatting is not kept in the InputBox.

For example, if A1 reads: "$10.00", the InputBox will just say "10".

Is there a way to have the macro keep/transfer over the formatting as well?

Cheers,

- I Love Lagar -

[JBeaucaire]

"Keep" the formatting? Not that I know of. Formatting is a "display" trick.

But you can format in VBA the same way you format in a cell. You already know that since you did it in your existing macro with the date:

Format(Date, "ddmmmyyyy")

Just play with the VBA for the other values and apply the formatting trick on each of the values you're using.

I use the macro recorder to figure out formatting VBA all the time. Set the cell back to "General" format, turn on the recorder, let it record you putting the formatting you want back on the cell. Tweak the code from there.

[ilovelagar]

Got it all to work!

Thank you so much eh, cheers

- I Love Lagar -