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

Link tool from userform with shape into sheet

0

hello

I try  link  checkboxes  with  shapes     but  I m stuck  with  this  code 

so  what  I want  when  select  checkbox1   then  select   shape 1   and  when  select  checkbox2  then  select shape2  and   unselected checkbox1 & shape  

so  if  any  body  has  suggestion please  provide me 

Private Sub CommandButton3_Click()
If CheckBox1.Value = True Then
sheet1.Shapes("shape1").OLEFormat.Object.Value = ChrW(&H2713)
CheckBox2.Value = False
sheet1.Shapes("shape2").OLEFormat.Object.Value = ""
ElseIf CheckBox2.Value = True Then
sheet1.Shapes("shape2").OLEFormat.Object.Value = ChrW(&H2713)
CheckBox1.Value = False
sheet1.Shapes("shape1").OLEFormat.Object.Value = ""
End If
End Sub
excel
MS Office Version: 2016
posted by: MAKLIL (rep: 34) on Wed Mar 10, 2021 at 2:58 am
Answer
Discuss

Discussion

Maklil

I assume you want the checkboxes on the Userform to "toggle" while it is displayed, right? (If so, why not use two radio buttons?) Do you need the text on shapes to change after CommandButton3 is pressed (or while the Userform is displayed)?
John_Ru (rep: 6142) Mar 10, '21 at 4:21 am
Add to Discussion

Answers

0
Selected Answer

Maklil

I assume you have reasons for not using radio buttons on the form (but want to toggle checkboxes) and that you want the shapes to be "ticked" (or "checked") when the checkboxes are clicked on the visible UserForm.

This needs a macro associated with each checkbox (as below).

Revision 1: the text of the rectangle "shape1" is set (and the other shape set to nothing) by the code below:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    sheet1.Shapes("shape1").TextFrame.Characters.Text = ChrW(&H2713)

    sheet1.Shapes("shape2").TextFrame.Characters.Text = ""
    Me.CheckBox2.Value = False
End If
End Sub
There's a similar code for the other checkbox (below but with "TextBox 2" set) and a simple sub to hide the UserForm when CommandButton3 is clicked(I don't know what else you want to do with that):
Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
    sheet1.Shapes("shape2").TextFrame.Characters.Text = ChrW(&H2713)
    sheet1.Shapes("shape1").TextFrame.Characters.Text = ""
    Me.CheckBox1.Value = False
End If
End Sub

Private Sub CommandButton3_Click()
Me.Hide
End Sub
All these bits of code are in the attached file.

Hope this is what you want.

posted by: John_Ru (rep: 6142) on Wed Mar 10, 2021 at 4:45 am
Discuss

Discussion

John
thanks  for  your  solution , but  i have  some  questions
1-  is  it  important  use textbox with shape   there  is  no  way  do  that  directly  by  shape  what  do  you  think  what  the  best  way  do  that ?
2-  when  I  select  check1 is  ok  but  when  I select checkbox2  it  doesn't  tick from the  first  time  it  ticks  from the  second  time  and  the  same  thing  with checkbox2  may  you fix it ,please?
MAKLIL (rep: 34) Mar 10, '21 at 6:17 am
Maklil

Please see Revision 1 to my Answer (and the revised file). There was an error in the code for Sub CheckBox2_Click() and I've realised that the text of the shape can be set directly (without an additional, grouped textbox).

Thanks again for selecting my Answer (but note you don't need to do that just to discuss refining the answer if it doesn't quite work)
John_Ru (rep: 6142) Mar 10, '21 at 6:48 am
John 
Aawsome !!  this  is  better   I see  many  your  contribution  for  help  many  members    I  appreciate   that 
thanks  so  much
MAKLIL (rep: 34) Mar 10, '21 at 6:56 am
Glad it works for you now, Maklil.
John_Ru (rep: 6142) Mar 10, '21 at 7:02 am
Add to Discussion


Answer the Question

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