Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

fill shape into sheet from label on userform

0

hello 

I search  for  a way  to  fill  the  shape  into  sheet  from  label on userform  

so  i design  a simple file    and  i  create  this  but  it  gives  me  error 

Private Sub CommandButton1_Click()
ActiveSheet.Shapes("Rectangle1").OLEFormat.Object.Formula = Label1.Caption
End Sub

thanks in advance 

excel
MS Office Version: 2010
posted by: speed (rep: 40) on Tue Mar 9, 2021 at 5:33 am
Answer
Discuss

Answers

0
Selected Answer

Speed

Revision 1: In the revised version of your file, I've used this code to set text directly  in your rounded Rectangle1 shape (with changes in bold):

Private Sub CommandButton1_Click() ActiveSheet.Shapes("Rectangle1").TextFrame.Characters.Text = Label1.Caption End Sub
Hope this works for you.
posted by: John_Ru (rep: 6142) on Tue Mar 9, 2021 at 7:43 am
Discuss

Discussion

John
this  is  nice  trick  ,   in the  begining  I  don't  understand  when  you  said  put  the  textbox    I'm  asking  with  myself  what  you're talking  about  it
I  have  shape  not  textbox   I  do  not note  where  is  the  textbox  you're  talking  about  it  then  when I  press the  shape  I  noted  there is   another tool inside  the  shape  this  is  great  work  you're  really a profesional    thanks  so  much
speed (rep: 40) Mar 9, '21 at 8:32 am
Glad you liked it Speed. thanks for selecting my Answer too.
John_Ru (rep: 6142) Mar 9, '21 at 9:27 am
I have to say I wasn't sure why you'd want to take the text from a Label control on your Userform (since the text in a Label is normally fixed unless changed by VBA). Personally I'd use the same method but copy user variable text from a TextBox control on the Userform.
John_Ru (rep: 6142) Mar 9, '21 at 9:31 am
John
to  understand  why   I  want to take the text from a Label control on your Userform actually  I design payment   voucher and   I  have  textbox   contains   amount  it should  show  amount  by letters   and  copy  to the sheet that's it
speed (rep: 40) Mar 9, '21 at 1:36 pm
Okay Speed. Sounds good 
John_Ru (rep: 6142) Mar 9, '21 at 2:55 pm
Speed

Please see Revision 1 to my Answer (and the revised file). In answering a similar question from Maklil today, I've realised that the text of the shape can be set directly (without an additional, grouped textbox).
John_Ru (rep: 6142) Mar 10, '21 at 6:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login