Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code in excell
I have a spreadsheet with a few check boxes that have macro attached to them.
When a check box is checked, a macro runs that makes the font in a corresponding cell, bold. A cell link is also attached to each check box to set a value for anothe cell on the page. I have about 20 of these boxes and have listed the code I use to bold the font in the cell: Sub CheckBox88_Click() Range("B23").Font.Bold = Range("A23").Value End Sub What I would like to do, is make one sub routine that I could use for every check box rather that having 20 of these little subs. I cannot figure out the syntax or how to do this. Does anyone have any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code in excell
Take a look at Application.Caller, you can test that to see which checkbox
has been clicked, and do a Select Case on the value Select Case Application.Caller Case "Check Box 1": MsgBox "Check Box 1" Case "Check Box 2": MsgBox "Check Box 2" Case "Check Box 3": MsgBox "Check Box 3" End Select -- HTH RP (remove nothere from the email address if mailing direct) "JCanyoneer" wrote in message ... I have a spreadsheet with a few check boxes that have macro attached to them. When a check box is checked, a macro runs that makes the font in a corresponding cell, bold. A cell link is also attached to each check box to set a value for anothe cell on the page. I have about 20 of these boxes and have listed the code I use to bold the font in the cell: Sub CheckBox88_Click() Range("B23").Font.Bold = Range("A23").Value End Sub What I would like to do, is make one sub routine that I could use for every check box rather that having 20 of these little subs. I cannot figure out the syntax or how to do this. Does anyone have any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code in excell
Thank you. I had never dealt with this property before. In my case, if I have
20 check boxes, I will need 20 cases, right? This is what I tried to use to make a generic routine that all the check boxes could use: Sub Bold() Dim CaseNum As String CaseNum = Application.Caller CaseNum = Right(CaseNum, 2) Range("B" & CaseNum).Font.Bold = Range("A" & CaseNum).Value End Sub Can you help me understand what I have done wrong here? "Bob Phillips" wrote: Take a look at Application.Caller, you can test that to see which checkbox has been clicked, and do a Select Case on the value Select Case Application.Caller Case "Check Box 1": MsgBox "Check Box 1" Case "Check Box 2": MsgBox "Check Box 2" Case "Check Box 3": MsgBox "Check Box 3" End Select -- HTH RP (remove nothere from the email address if mailing direct) "JCanyoneer" wrote in message ... I have a spreadsheet with a few check boxes that have macro attached to them. When a check box is checked, a macro runs that makes the font in a corresponding cell, bold. A cell link is also attached to each check box to set a value for anothe cell on the page. I have about 20 of these boxes and have listed the code I use to bold the font in the cell: Sub CheckBox88_Click() Range("B23").Font.Bold = Range("A23").Value End Sub What I would like to do, is make one sub routine that I could use for every check box rather that having 20 of these little subs. I cannot figure out the syntax or how to do this. Does anyone have any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code in excell
Thank you very much, I have never worked that property before. I can see it
could be used for a lot of things. I would like to make a generic routine that all of the checkboxes could run, here is what I came up with: Sub Bold() Dim CaseNum As String CaseNum = Application.Caller CaseNum = Right(CaseNum, 2) Range("B" & CaseNum).Font.Bold = Range("A" & CaseNum).Value End Sub Can you help me figure out what is wrong with this? I am not familiar with the syntax on setting the Range Property. "Bob Phillips" wrote: Take a look at Application.Caller, you can test that to see which checkbox has been clicked, and do a Select Case on the value Select Case Application.Caller Case "Check Box 1": MsgBox "Check Box 1" Case "Check Box 2": MsgBox "Check Box 2" Case "Check Box 3": MsgBox "Check Box 3" End Select -- HTH RP (remove nothere from the email address if mailing direct) "JCanyoneer" wrote in message ... I have a spreadsheet with a few check boxes that have macro attached to them. When a check box is checked, a macro runs that makes the font in a corresponding cell, bold. A cell link is also attached to each check box to set a value for anothe cell on the page. I have about 20 of these boxes and have listed the code I use to bold the font in the cell: Sub CheckBox88_Click() Range("B23").Font.Bold = Range("A23").Value End Sub What I would like to do, is make one sub routine that I could use for every check box rather that having 20 of these little subs. I cannot figure out the syntax or how to do this. Does anyone have any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code in excell
That looks the way to go. I think this is what you want
Sub Bold() Dim CaseNum As String CaseNum = Application.Caller CaseNum = Right(CaseNum, 2) Range("B" & CaseNum).Font.Bold = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "JCanyoneer" wrote in message ... I have a spreadsheet with a few check boxes that have macro attached to them. When a check box is checked, a macro runs that makes the font in a corresponding cell, bold. A cell link is also attached to each check box to set a value for anothe cell on the page. I have about 20 of these boxes and have listed the code I use to bold the font in the cell: Sub CheckBox88_Click() Range("B23").Font.Bold = Range("A23").Value End Sub What I would like to do, is make one sub routine that I could use for every check box rather that having 20 of these little subs. I cannot figure out the syntax or how to do this. Does anyone have any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code in excell
Thans for the help. I couldn't get my original try to work because the check
boxes kept losing their their names. I got that fixed and found that it worked fine. I left the bold property = the value of the link cell so that the checked/unchecked would bold/unbold. Thank you so much for your help. "Bob Phillips" wrote: That looks the way to go. I think this is what you want Sub Bold() Dim CaseNum As String CaseNum = Application.Caller CaseNum = Right(CaseNum, 2) Range("B" & CaseNum).Font.Bold = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "JCanyoneer" wrote in message ... I have a spreadsheet with a few check boxes that have macro attached to them. When a check box is checked, a macro runs that makes the font in a corresponding cell, bold. A cell link is also attached to each check box to set a value for anothe cell on the page. I have about 20 of these boxes and have listed the code I use to bold the font in the cell: Sub CheckBox88_Click() Range("B23").Font.Bold = Range("A23").Value End Sub What I would like to do, is make one sub routine that I could use for every check box rather that having 20 of these little subs. I cannot figure out the syntax or how to do this. Does anyone have any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to change the normal excell icon on an excell file? | Excel Worksheet Functions | |||
create a slides show with excell spreadsheets using excell | Charts and Charting in Excel | |||
Excell VBA code | Excel Discussion (Misc queries) | |||
controlling another application via VBA code in Excell & sending keystrokes & mouse clicks | Excel Programming | |||
Autorefreshing excell external data while opening from VBA code | Excel Programming |