?
Solved

excel reference checkbox, vba

Posted on 2012-08-30
11
Medium Priority
?
1,464 Views
Last Modified: 2012-08-30
Dear experts,

i think it should be very simple to reference a checkbox but i don't seem to get it to work. i want to reference a checkbox on a excelsheet. below is my code

Sub CheckBox1_Click()

If Workbooks("Book1").Worksheets("Deals Calc").CheckBox1 = True Then
Workbooks("Book1").Worksheets("Settings").Range("L1").Value = 1

Else
Workbooks("Book1").Worksheets("Settings").Range("L1").Value = 13
End If

End Sub

Open in new window


i get runtime error 438

cheers,

Mark
0
Comment
Question by:MarkVrenken
  • 6
  • 3
  • 2
11 Comments
 
LVL 3

Expert Comment

by:EricLynnWright
ID: 38349982
you look to be referencing your control correctly.  However, it's probably the path build up to  the control.  Check the name of the control - is it checkbox1.  Check the spelling of the worksheet and make sure it's ok.


and did you save the workbook to a different name (other than book1)?
0
 
LVL 3

Expert Comment

by:EricLynnWright
ID: 38350000
might change this ...

   If Workbooks("Book1").Worksheets("Deals Calc").CheckBox1


to...

   If activeworkbook.activesheet.CheckBox1
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38350007
This is really stupid but i don't know where i can find/change the name of the control

The name of the workbook is referenced the right way, but i changed the name to book1 before posting it here
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 1

Author Comment

by:MarkVrenken
ID: 38350022
changing it to    If activeworkbook.activesheet.CheckBox1 doesn't make a difference
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38350045
i think it's in the upper left corner next to the formula bar right? that says CheckBox1
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38350069
ActiveSheet.Shapes("Checkbox1").OLEFormat.Object.Object.Value is working.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38350104
If the code is in the module of the worksheet that the control is on you can just use the name, CheckBox1.
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38350365
does anyone have an idea of why it only works like i do it?
0
 
LVL 3

Accepted Solution

by:
EricLynnWright earned 800 total points
ID: 38350400
0
 
LVL 1

Author Closing Comment

by:MarkVrenken
ID: 38350494
Thanks:)
0
 
LVL 35

Expert Comment

by:Norie
ID: 38351014
I added a Forms checkbox and ActiveX checkbox to the same worksheet, not at the same time.

This only worked with the ActiveX checkbox:
ActiveSheet.Shapes("Checkbox1").OLEFormat.Object.Object.Value

Open in new window

When I tried it with the Forms checkbox it result in an 'Object doesn't support this property or method' error.

Like I said, if the code is in the same worksheet as the worksheet the checkbox is on you can just use it's name - you even get intellisense.

If you are using a Forms checkbox then you can use the sheets Checkboxes collection - see the attached file.
Checkboxes.xlsm
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question