Having trouble adding toggle buttons to worksheet

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi Guys.
I am in the middle of building a pastein sheet which will check for duplicates before the pasted in data is processed.
As part of this I placed a "Show" button next to each row pasted in. The show button will bring out the matches and display it below the pasted in area in a split window.

Up until this is working.
Now I also have to place either a radio button or a toggle button so the person can decide to upload the row or discard it at the final submission.

I noticed that toggle buttons are OLEObjects so I declared my "Togglebutton" as OleObject

Then used this code to add the toggle button: (It is inside of a loop. cl giving us the row.)

VBA Code:
'===============Button Placement========================
        'Adding the show button
        Set ShowButton = Sheet2.Buttons.Add(Top:=cl.Offset(0, 14).Top, Left:=cl.Offset(0, 14).Left, Height:=cl.Offset(0, 14).Height, Width:=cl.Offset(0, 14).Width)
            With ShowButton
                .Caption = "SHOW"
                .OnAction = "Showme"
            End With
        'Adding the toggle buttons
        Set Togglebutton = Sheet2.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, _
        DisplayAsIcon:=False, Top:=cl.Offset(0, 15).Top, Left:=cl.Offset(0, 15).Left, Height:=cl.Offset(0, 15).Height, Width:=cl.Offset(0, 15).Width)
        
        With Togglebutton
            [B].Caption = "TEST"[/B]
            .Font.Size = 10
            .Value = 0
        End With
        
'===============Button Placement End====================

It does place the button, but it craps out with object doesn't support this method. I believe at the Caption line.

My questions are:
Does toggle buttons using the textframe.text method?
I have seen some posts that claiming it is only a caption. That I can change based on the value.
Also if my toggle button will have the same .OnAction property?

Thanks

Thomas
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
My questions are:
Does toggle buttons using the textframe.text method? NO
I have seen some posts that claiming it is only a caption. That I can change based on the value. Caption is changeable.
Also if my toggle button will have the same .OnAction property? NO Use either _Click better is _Change

Thanks

Thomas
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Thanks CSmith.
I can't get my head around why my Togglebutton OLEobject doesn't have a .caption property that I can set?
However when I enter design mode on the sheet, and check the toggle button property, there is a caption line.

I checked in the intellisense, no caption.
I tried to set Togglebutton.caption ->Error.
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Try:
VBA Code:
        With Togglebutton
            .Object.Caption = "TEST"
            .Font.Size = 10
            .Value = 0
        End With
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Thanks CSmith. This is actually working with the Object.
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Welcome and thank you for updating us! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top