VBA Using IF THEN within a textbox - Spreadsheet (not user form)

[TBRoberts]

I am most likely going about this all wrong but here goes nothing...

I am trying to pass a dynamic value into a textbox.

My textbox reads something like this:


Case(s) A, B, C, D generated given scenario(s) 1, 2, 3. There was/were X ProductType1 and Y ProductType2 totaling $$$$


I have my code as follows:

Dim CaseNum, Scen, NumProductType1, NameProductType1, NumProductType2, NameProductType2, Product1Total, Product2Total as string

    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 39, 129.75, 503.25 _
        , 182.25).Select


        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text _
    = "Case(s) " & CaseNum & " generated via the " & Scen & " scenario(s). " & "There were/was " & NumProductType1 _
    & NameProductType1 & "and " & NumProductType2 & NameProductType2 & "Totaling " & Format (Product1total, "currency") _
 & Format (Product2total, "currency") & "respectively."

I would like to pass into the text an IF statement that would allow the text to read more smoothly. For example, When there is only one case, scenario, or product type passed into the text, I would like it to get rid of the "(s)" and just make the text singular, and just keep one product type. For example, if there was only one case, scenario and product the text would read: "Case xxx alerted via the yyy scenario. There was one zzzz totaling $$$. Is there a way to pass an IF Then statement into a textbox???

Hope this makes sense. As always, thank you for your help!!

 

[ranman256]

i dont think this is a good way to use a text box.
they are pretty simple.

 

[codeliftsleep]

i dont think this is a good way to use a text box.
they are pretty simple.

I second that. This is poor design.

 

[TBRoberts]

Some suggestions would be appreciated

 

[codeliftsleep]

Some suggestions would be appreciated

I'm not understanding what you are trying to accomplish with this. Why would you need a text box? Text boxes are for people to enter data.