Excel vba get activex button from another sheet
- #Excel vba get activex button from another sheet how to#
- #Excel vba get activex button from another sheet code#
To copy a button from one sheet to another preventing the automatic name change use the below code. OLEObjects(.OLEObjects.Count).Name = xName Set x = Sheets("SRC").OLEObjects("HasCustomName") Note: you cannot have two objects of the same name on one spreadsheet. With (ActiveSheet.Name).You can copy an ActiveX Control from one sheet to another with the below code. 'add macro at the end of the sheet module Activate End With Application. OLEObjects(.OLEObjects.Count).Name xName. 'macro text to be added possibly by array?Ĭode = "Private Sub TestButton_Click()" & vbCrLf Sub CopyActiveX() Application.ScreenUpdating False Dim x As OLEObject, y As OLEObject Set x Sheets('SRC').OLEObjects('HasCustomName') Set y x.Duplicate Dim xName As String xName x.Name y.Cut With Sheets('TRGT'). Set Obj = (ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=cellLeft, Top:=cellTop, Width:=cellWidth, Height:=cellHeight)ĪctiveSheet.OLEObjects(1).Object.Caption = "Test Button"
On the worksheet, drag the pointer, to add a button of a specific size, or click to create a default sized button. In the Form Controls section, click Button. It may take some small tweaks, but will get the job done. In the Controls group, click Insert, to see the Form Controls and ActiveX Controls. Here is an example of ActiveX buttons being created and coded to run. Set objCmdBtn = Sheets("Sheet1").OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Set Rnge = ActiveSheet.Range(Cells(i + 1, 1), Cells(i + 1, 1)) Next objCmdBtn 'TypeName Function returns the data-type about a variable - TypeName(varname is objCmdBtn) If TypeName(objCmdBtn.Object) = "CommandButton" Then objCmdBtn.Delete Set ColumnRange = Range("A:A") ' Set width & height of column AĬolumnRange.ColumnWidth = 5: ColumnRange.RowHeight = 15.75įor Each objCmdBtn In ActiveSheet.OLEObjects VBA so that each checkbox uses the same click event code, but that isĪnd if I read Jason's post above, this is what he's questioning regarding the event code.Īny assistance welcomed in completing this code, as I have Not yet seen a working example which interlocks it to a single event, as per the form button module above. Let's say you want to store a list of your favourite beverages in a single variable. The values must all be of the same data type. It seems I've now come to the same stand still they did as descibed in their last post : Array is a memory location capable of storing more than one value.
#Excel vba get activex button from another sheet code#
I'm posting some code that I've been working on based on this post here: Multiple active X checkboxes. If Not Application.Intersect(Target, Range("B2:B12")) Is Nothing Then Public Click_Button As Variant ' Make Variable Public for Userform1Īnd the next option to be considered is a range detection Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set buttons = (rng.Left, rng.Top, rng.Width, rng.Height) Set rng = ActiveSheet.Range(Cells(i, 1), Cells(i, 1)) Sub ColumnA_Buttons() Dim buttons As Button
#Excel vba get activex button from another sheet how to#
How to add a button programmatically in VBA next to some sheet cell data? What I have been trying to achieve is a version of code employing "activeX" Command Buttons, but after having studied some great tutorials and poured over a range of programing concepts, I still fail miserably to employ OLEObjects. However, a mouse click over this Range will obviously activate any code from within the Worksheet_Selection Change procedure.Sorry just stating the obvious! The second post uses the Range function, but obviously doesn't incorporate any buttons to interactive with. Once operational the column of buttons will correspond to relative data on the same row, and when clicked will open corresponding folders, and userforms in later developments. This exemplifies what I consider my most favored example with working buttons. I have posted one such working solution below which makes use of "form type buttons" and a Modules. So thanks for the collection of solutions and ideas.īasically, I have a spread sheet application requiring the first column, Column A, to be filled with "Active X" buttons in every cell, looping through for a given quantity.
My first post here, but have been successfully sourcing solutions and ideas from this website for a while now.