VBA Custom Function - Form Control Name Object Not Supported?

Jun 18, 2014

I'm creating a custom function to cut down on redundant code. However, in converting the code to a function so that it may be used in different instances, I'm getting an unexpected error when the code executes.

The error is:

Code:
Object doesn't support this property or method.

Here is my function:

Code:
Public Function chkClick(checkboxName As String, tabName As String, chartName As String, seriesNumber As Long)
Sheets(tabName).ChartObjects(chartName).Activate
If Sheets(tabName).checkboxName.Value = True Then
ActiveChart.FullSeriesCollection(seriesNumber).Select

[code]....

The function checks the status of the checkbox name passed to it. If it's checked, it will show a chart series. If it's not checked, it will hide the chart series. The error comes in referencing the "checkboxName".

How might I need to alter the code so that I can use the form control name as a variable? This is ultimately one of the key components of the function as each control has it's own name.

View 9 Replies


ADVERTISEMENT

Pass Custom Object To UDF

Aug 17, 2008

I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.

Here is my class (I know I can do better with access modifiers/ properties in a serious version):

'The class is called ConsCell

Public Car As Variant
Public Cdr As Variant

Private Sub Class_Initialize()
End Sub

Here is the code from the module which tries to call it:

Function MakeCell()
Cell = New ConsCell 'Execution always stops here-- but with no error message
Cell.Car = 15
Cell.Cdr = "NIL"
MakeCell = Cell
End Function

Function PrintCell(c As ConsCell)
PrintCell = c.Car
End Function

The code for a cell in the spreadsheet itself is this:

=PrintCell(MakeCell())

But it gives me the #VALUE! error.

View 9 Replies View Related

ActiveX Control Can't Create Object

Jul 28, 2009

I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my

View 14 Replies View Related

USE Calendar Control 10.0 Object In Worksheets

Jul 19, 2006

I'm trying to use the Calendar Control 10.0 object in one of my worksheets. I basically want to be able to display a value by clicking on one of the dates in the calendar. Example: Say I want the contents of cell A1 to be displayed in B1 when somebody clicks on the 17th of February 2006. Is there any way to do this?

View 5 Replies View Related

Control Object Event Handler

Aug 22, 2006

Is there an Event Handler that runs a macro whenever any control object in a worksheet has its state changed or whenever any checkbox is checked/unchecked. I want to have a method handle this outside of the checkbox since the number of checkboxes are dynamic and should not have to be individually coded by the user.

View 5 Replies View Related

Custom Checkbox Control In Excel

Mar 6, 2006

I have a user who is asking if it is possible to create a custom checkbox control for use inside of Excel. This new checkbox control is essentially a checkbox control that has other graphics around the checkmark other than a square.

For instance, he need to have a triangle that has a clickable checkmark inside it. He needs a circle with a clickable checkmark inside as well as a couple of other shapes that have a clickable checkmark inside (no square around the checkbox). I've tried to put a regular checkbox on top of a graphic but you have to deal with the square corners of the checkbox even if it's set to transparent.

Any code or otherwise on how to create a custom or add-in that would do what I describe?

View 6 Replies View Related

Use Checkbox To Control Custom Views?

Sep 14, 2012

I am trying to use a checkbox to control the custom views that i have set ("Normal", "Hide") However after i entered it in VBA, it just does not work. After i check or uncheck the checkbox, it just keep going back to the "Normal" view.

Sub CheckBox1_Click()
If CheckBox1 = True Then
ActiveWorkbook.CustomViews("Hide").Show
Else
ActiveWorkbook.CustomViews("Normal").Show
End If

End Sub

View 5 Replies View Related

Custom Date Picker Control

Mar 29, 2007

I'm building a userform excel application and am using the MS date/time picker control. I've run into the familiar "computer doesn't have OCX" problem and want to avoid using a custom OCX.

I was thinking of just making my own control instead. Has anyone made one in VBA? I found some VB examples which I could port over, but they use a lot of api calls. For reliability, I'd like to keep this as simple as possible though.

View 9 Replies View Related

Control Order Of Class Object Events

Aug 19, 2008

Split from Determine Order Of Event Procedures

I realise that there's no set order for different types of event, because, as you say, it basically depends on what the user does. But what I'd like to control is what order like events fire in.

So let's say that object_1 handles the aplApp_WorkbookNewSheet event (an application-wide event).

Let's say that object_2 is an object of the same type as object_1, and therefore also handles the aplApp_WorkbookNewSheet event.

The objects are entirely independent; they know nothing about each other. However, I would like to be able to control whether the aplApp_WorkbookNewSheet event is fired first in object_1 or first in object_2 when the user triggers this event by adding a new worksheet somewhere.

View 4 Replies View Related

Custom View Error. Cannot Shift Object Off Sheet

Jun 15, 2008

I have a very wide sheet in which I have hidden and saved seven views.I am using it for a very long time but when i did some edit work in the 'full' view,the other views are not showing. In stead I am getting the messages "cannot shift objects off sheet" and "some view settings could not be applied".This is very frustrating since a lot of patience was tested while making this wide data.

View 2 Replies View Related

Multiple Instances Of Excel And ActiveX Control Object

Feb 21, 2014

I have an excel application which works extremely well in one single instancec. It has a bunch of ActiveX control objects on the worksheet. Generally I call them by convention Sheet1.Cmd.show.

I have since experimented with having multiple instances of excel running on the machine. I start to notice something very strange. The first instance I open and run all the activeX buttons are all clickable. As soon as I open a second instance of an excel program all the activeX buttons on the worksheet are not even clickable. They are enabled by my program, but not clickable as if they were not there at all.

I also try open a normal excel workbook then followed by my excel program in a second instance. Even though the first excel file does not utilize any activeX objects all my buttons on the subsequent instance are not clickable.

The weird thing is all my activeX control objects on a userforms are still clickable and call to all my planned events in my program. It is just all the activeX controls on my worksheet that do not have any reaction at all.

I always think when one opens a new instance it will be totally indpendent and would even occupy another CPU if my machine has more in it. If they are truly independent then I am at a loss to understand why my activeX controls would fail to work at all.

View 8 Replies View Related

Change Toolbar Custom Control Button Color

Jun 23, 2005

I am using the following code to create an Excel toolbar custom control Button. What would be the VBA code to change the color of the image I have chosen? ....

View 6 Replies View Related

Add/Remove Custom Control To Chart Drop-Down Menu

Sep 1, 2007

I want to add a control under Chart on the menu bar. This line of code errors with "Invalid Procedure Call or Argument"

With Application. CommandBars("Worksheet menu bar").Controls("Chart")

Change "Chart" to "File" or "Edit" or "Tools" and it's fine.

The Chart item only appears when a chart is selected, but it errors even when a chart on the worksheet is selected.

View 7 Replies View Related

Form Control Listbox VBA

Oct 23, 2009

i have a form control listbox (list box 5), it is multi select, i need to create a for next statement that loops through the list in and tells me which "row numbers" as it were are selected. i.e if the 1st and 3rd ones are blue, it returns 1,3 in a cell? (lets say cell A1).

View 2 Replies View Related

Can Reverse Form Control

Dec 1, 2012

I have a control sheet that I am building and my sliderbar is making my data just disapear and not replacing it with any new data. Can this be fixed if I am able to reverse the sliderbar

View 2 Replies View Related

Use Of MS Chart Control In VBA Form

May 12, 2006

I am trying to use the Chartspace object on a VBA form in Excel 2002, but am unable to find out how to specify the speadsheet data to be used for each series. I have found out how to add series, and to add titles & legend etc.

View 5 Replies View Related

Launch Form From Custom Toolbar?

Oct 3, 2012

I have a custom toolbar with a button on it that I want to open a userform from.

I have tried this macro

VB:

Code:
Sub Admin_Show()
frmAdmin.Show
End Sub

[Code]....

View 3 Replies View Related

Run Macro When Form Control Checkbox Changes

May 30, 2013

I have many Form Control Check Boxes that all link to another sheet on row 3.

I have many changes to make but only want to implement the change related to the check box.

This code works perfect when you manualy type true or false on row 3 but not if the check box makes the change.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisCol = Target.Column
If Target.Row = 3 Then
RESULT = MsgBox(Cells(1, ThisCol) & " = " & Cells(3, ThisCol), vbOKOnly, "CLICK RESULTS")
End If
End Sub

Why does this not work when a check box changes the value in row 3?

View 5 Replies View Related

Form Control Button Changes Size

Nov 16, 2012

I have several files with form control buttons that automate functions but, on occasion, they get smaller relative to the worksheet they're in. At some point, they become unuasable unless they're manually resized.

View 8 Replies View Related

Search For A Form Control Button

Nov 28, 2012

I have a rather large sheet with lots of embedded form control buttons, each one with a document link. Is there a function (no VBA) to search for a specific form control button in excel? for example: i want to find the form control button which is linked to the "application" document.

View 8 Replies View Related

Form Control Like The One Used In System Time

Jan 13, 2009

Does anyone know how to make a form control (ex combobox, textbox) have multiple columns so that it would behave like 4 controls in one. What im going for is a control that looks like the control used when setting windows system time "12:30:00 AM" So "12" is in col 1, ":" is in col 2, "30" is in col 3, ":" is in col 4, "00" is in col 5, and "AM" is in col 6.

View 7 Replies View Related

User Form Cursor Control

Feb 13, 2008

how to link VBA to the buttons on user forms:

[url]

Now I'm interested in some refinements to make this little toy I'm building work faster. I want the user to be able to enter data without having to click the text box in order to proceed. My code

Userform1. show

just brings up the form with a text box in it, and the user has to click in the box to get started. Is there code I can add that will put the cursor in the text box so it's ready to go?

I suppose this seems trivial, but it will speed the data entry part of this little project up and every little bit counts.
.
.
.

View 9 Replies View Related

Link 2 Form Control Boxes

Mar 15, 2009

I am looking to connect 2 form control boxes and have the second box run 2 different types of macros. The first box will have only 2 options - select by week and select by month.

The second box should show the list of weeks or months based on the selection in the first box. Then for the second box, if weeks is shown, a week macro should be run whenever a week date is selected. Similarly for the list of months, a month macro should be run whenever a month is selected.

I have been trying to do this for more than a week (after posting on this board) without success. I apologize if this seems like a duplicate post.

View 9 Replies View Related

Worksheet Form Control Properties

Sep 21, 2006

how to get a list of the properties for form control shapes (not control toolbox shapes) that are placed on a worksheet (not on a userform). Eg., a button, checkbox, combobox, etc.

If it's possible, I'm interested in working with properties like "enabled", "caption", etc. that aren't listed on the "Format Control Properties" dialog.

I understand you can edit properties of a form control shape via VBA code (See example below), however, I can't seem to find anything within the object browser about them.

EXAMPLE
With ActiveSheet.Shapes("Scroll Bar 2").ControlFormat
.Min = 10
.Max = 150
End With

View 5 Replies View Related

Extract Data With Form Control

Sep 22, 2006

I need to extract proper unit price of a component from a large data base. So far, I have created drop down lists so that the users can select different parameters for each component. How do I use the user selected parameter to pin point the proper unit price from the large database?

View 3 Replies View Related

Create A Custom Data Entry Form

Jan 30, 2008

I am having trouble creating a custom data entry form in excel. What steps would I need to take..

Attached is a example of the data, the Headers are in bold, the highlighted columns are to be drop boxes.

View 11 Replies View Related

Showing User Form In A Custom Module

Jan 27, 2009

MyUserForm.Show

The above code works in a worksheet code section but not in a custom Module? Why? How can I make this call outside of the Worksheet section.

View 2 Replies View Related

Pop-Up Form With User Input - Inventory Control

Jul 16, 2014

I am working on an Inventory control worksheet where i have all parts used in a certain assembly on the left (A) followed by Qty per assembly (B) and then current baseline Inventory (C). In columns D-max i will have at the top a pull down menu to let the user decide if they are subtracting from inventory (Purchase Order) or adding to inventory (Fulfillment). Under both circumstances, depending on which is selected I would like a different form to pop up which allows the user to enter values to control the chart.

If Purchase order is selected then a pop up will ask the order number, date, and quantity. This will then fill in three specific cells in the chart which control an equation to subtract the number of parts based on the order quantity.

If Fulfillment is selected I would like a pop up or the entire parts list to appear with a field to enter the number of parts being stocked. The program will then add these parts to the previous inventory entry.

In the example attached you can see that right now you have to manually enter the number of units ordered, then an If statement takes over to calculate the new inventory level (If statement used to keep things neat and possibly to incorporate fulfillment at a later time). At this time there is no way for me to enter stocking transactions.

View 1 Replies View Related

How To Protect Form Control Button With Password

Jul 21, 2014

I have made an excel sheet for multiple choice questions, in which person needs to click on submit button after completing with the test & pop up will say "Your score is__". Now, I want to want to add one more button named "Reset". I want below :

results:

1. Person should not be able to make any changes after clicking on "Submit" button.

2. If one tries to click on "Reset" button, it should ask for password.

3. After keying password for "Reset" button, all questions should be reset so that next person can give test.

I have attached an example excel sheet for reference : DoM1.xlsm‎

View 2 Replies View Related

Insert NICE Form Control Buttons

Oct 8, 2009

how to insert an attractive form control button instead of the horrible default grey buttons that come with Excel? I've seen it done, so I know it can be done.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved