change font to bold and size when transferring from userform to sheet

[Mikeymike_W]

Hi,

I have a userform, i have it set up to tranfer details from the userform to a sheet within the same workbook.
I was wondering how i would go about changing the font to bold and change the font size of Label1.caption once it is transferred to the sheet?

Many thanks for your help,

Mike

Sheets("12 Month").Shapes("TBQA1").OLEFormat.Object.Text = Label1.Caption & vbNewLine & vbNewLine & TextBox2.Value & vbNewLine & TBDisc1.Value

 

[severynm]

Are you changing the font of the label in the userform? Can't you use Label1.FontBold = True and Label1.FontSize = ## in the userform code when the user submits the data?

 

[Norie]

Is it only the label caption you want to bold when you set the text of the label with the values from the userform?

 

[Mikeymike_W]

Hi Guys,

I dont want to change the font in the userform, only text/caption of the label once it is transferred onto the sheet "12 Month".

Thanks for your help

 

[Norie]

I wasn't talking about the userform.

From the code you posted it appears you are transferring data to a shape, e.g. label, on a worksheet.

So, is it only the label caption you want to bold when you set the text of the shape?

 

[Mikeymike_W]

I wasn't talking about the userform.

From the code you posted it appears you are transferring data to a shape, e.g. label, on a worksheet.

So, is it only the label caption you want to bold when you set the text of the shape?

yeah, just the label caption i want to alter when transferred over, i'm looking to change to bold and change the font size to 16.

 

[Norie]

What type of shape is 'TBQA1'?

 

[Mikeymike_W]

What type of shape is 'TBQA1'?

its a textbox

 

[Norie]

This should work.

Dim shp As Shape

Set shp = Sheets("12 Month").Shapes("TBQA1")

With shp.OLEFormat.Object
    .Text = Label1.Caption & vbNewLine & vbNewLine & TextBox2.Value & vbNewLine & TBDisc1.Value
End With

shp.TextFrame.Characters(1, Len(Label1.Caption)).Font.Bold = True

 

[Mikeymike_W]

This should work.

Dim shp As Shape

Set shp = Sheets("12 Month").Shapes("TBQA1")

With shp.OLEFormat.Object
    .Text = Label1.Caption & vbNewLine & vbNewLine & TextBox2.Value & vbNewLine & TBDisc1.Value
End With

shp.TextFrame.Characters(1, Len(Label1.Caption)).Font.Bold = True

Hi Norrie,

I tried out your code, it does make the label caption bold but unfortunately it seems to make all of the other values (Textbox2.value & TBDisc1.value) also bold.

Any ideas?

I really appreciate your support!