Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
I would like to create automatically a checkbox per row but also if
the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() On Error Resume Next Dim c As Range, myRange As Range Set myRange = Selection For Each c In myRange.Cells ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select With Selection .LinkedCell = c.Address .Characters.Text = "" .Name = c.Address End With c.Select With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE" .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked .Font.ColorIndex = 2 'cell background color = White 'I think here should be the hiding option but i don't know how to write it End With Next myRange.Select End Sub regards, DIANA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
On 20 June, 23:04, Dmhs wrote:
I would like to create automatically a checkbox per row but also if the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() * * On Error Resume Next * * Dim c As Range, myRange As Range * * Set myRange = Selection * * For Each c In myRange.Cells * * * * ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select * * * * * * With Selection * * * * * * * * .LinkedCell = c.Address * * * * * * * * .Characters.Text = "" * * * * * * * * .Name = c.Address * * * * * * End With * * * * * * c.Select * * * * * * * * With Selection * * * * * * * * * * .FormatConditions.Delete * * * * * * * * * * .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE" * * * * * * * * * * .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .Font.ColorIndex = 2 'cell background color = White * * * * * * * * * * *'I think here should be the hiding option but i don't know how to write it * * * * * * * * End With * * * * Next * * * * *myRange.Select End Sub regards, DIANA hi personally i don't think you can add that code to the check box in the way you want. to hide and unhide columns, you would have to use the check box click event to trigger the hide/unhide code to run. the code would have to reside in each check box which would be independent of the code that adds the check boxes. I suppose you could program the vb editor to add the code to each check box but i don't know how to do that. i do know it can be a real pain depending on what you are doing. you might check this site out for more info... http://cpearson.com/excel/vbe.aspx here is code i use for one of our engineering files. but i only have 1 check box. it not only hides/unhides a column, it also changes the color and captions of the check box. make adjustments as needed. Private Sub ChBx1_Click() If Columns("C:C").Hidden = True Then Columns("C:C").Hidden = False ChBx1.BackColor = RGB(0, 0, 255) ChBx1.ForeColor = RGB(245, 245, 5) ChBx1.Caption = "Metric" Columns("D:D").Hidden = True Else Columns("C:C").Hidden = True Columns("D:D").Hidden = False ChBx1.BackColor = RGB(245, 30, 5) 'ChBx1.ForeColor = ChBx1.Caption = "Standard" End If End Sub regards FSt1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
On Jun 20, 10:44*pm, FSt1 wrote:
On 20 June, 23:04, Dmhs wrote: I would like to create automatically a checkbox per row but also if the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() * * On Error Resume Next * * Dim c As Range, myRange As Range * * Set myRange = Selection * * For Each c In myRange.Cells * * * * ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select * * * * * * With Selection * * * * * * * * .LinkedCell = c.Address * * * * * * * * .Characters.Text = "" * * * * * * * * .Name = c.Address * * * * * * End With * * * * * * c.Select * * * * * * * * With Selection * * * * * * * * * * .FormatConditions.Delete * * * * * * * * * * .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE" * * * * * * * * * * .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .Font.ColorIndex = 2 'cell background color = White * * * * * * * * * * *'I think here should be the hiding option but i don't know how to write it * * * * * * * * End With * * * * Next * * * * *myRange.Select End Sub regards, DIANA hi personally i don't think you can add that code to the check box in the way you want. to hide and unhide columns, you would have to use the check box click event to trigger the hide/unhide code to run. the code would have to reside in each check box which would be independent of the code that adds the check boxes. I suppose you could program the vb editor to add the code to each check box but i don't know how to do that. i do know it can be a real pain depending on what you are doing. you might check this site out for more info... http://cpearson.com/excel/vbe.aspx here is code i use for one of our engineering files. but i only have 1 check box. it not only hides/unhides a column, it also changes the color and captions of the check box. make adjustments as needed. Private Sub ChBx1_Click() *If Columns("C:C").Hidden = True Then * * * * Columns("C:C").Hidden = False * * * * ChBx1.BackColor = RGB(0, 0, 255) * * * * ChBx1.ForeColor = RGB(245, 245, 5) * * * * ChBx1.Caption = "Metric" * * * * Columns("D:D").Hidden = True * * * * Else * * * * * * Columns("C:C").Hidden = True * * * * * * Columns("D:D").Hidden = False * * * * * * ChBx1.BackColor = RGB(245, 30, 5) * * * * * * 'ChBx1.ForeColor = * * * * * * ChBx1.Caption = "Standard" *End If End Sub regards FSt1- Hide quoted text - - Show quoted text - I'm not quite sure what you want because you can only hide entire columns, not columns in a row. This code will color the cell yellow and hide columns J:K if anything is inputed into the cell. Empty the cell for no color and unhide. No checkbox overhead needed. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("b2:b22")) Is Nothing _ Or Target.Count 1 Then Exit Sub With Target If Len(Application.Trim(Target)) < 1 Then .Interior.ColorIndex = 0 Columns("j:k").Hidden = False Else .Interior.ColorIndex = 6 Columns("j:k").Hidden = True End If End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
On Jun 21, 7:33*am, Don Guillett Excel MVP
wrote: On Jun 20, 10:44*pm, FSt1 wrote: On 20 June, 23:04, Dmhs wrote: I would like to create automatically a checkbox per row but also if the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() * * On Error Resume Next * * Dim c As Range, myRange As Range * * Set myRange = Selection * * For Each c In myRange.Cells * * * * ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select * * * * * * With Selection * * * * * * * * .LinkedCell = c.Address * * * * * * * * .Characters.Text = "" * * * * * * * * .Name = c.Address * * * * * * End With * * * * * * c.Select * * * * * * * * With Selection * * * * * * * * * * .FormatConditions.Delete * * * * * * * * * * .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE" * * * * * * * * * * .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .FormatConditions(1).Interior..ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .Font.ColorIndex = 2 'cell background color = White * * * * * * * * * * *'I think here should be the hiding option but i don't know how to write it * * * * * * * * End With * * * * Next * * * * *myRange.Select End Sub regards, DIANA hi personally i don't think you can add that code to the check box in the way you want. to hide and unhide columns, you would have to use the check box click event to trigger the hide/unhide code to run. the code would have to reside in each check box which would be independent of the code that adds the check boxes. I suppose you could program the vb editor to add the code to each check box but i don't know how to do that. i do know it can be a real pain depending on what you are doing. you might check this site out for more info... http://cpearson.com/excel/vbe.aspx here is code i use for one of our engineering files. but i only have 1 check box. it not only hides/unhides a column, it also changes the color and captions of the check box. make adjustments as needed. Private Sub ChBx1_Click() *If Columns("C:C").Hidden = True Then * * * * Columns("C:C").Hidden = False * * * * ChBx1.BackColor = RGB(0, 0, 255) * * * * ChBx1.ForeColor = RGB(245, 245, 5) * * * * ChBx1.Caption = "Metric" * * * * Columns("D:D").Hidden = True * * * * Else * * * * * * Columns("C:C").Hidden = True * * * * * * Columns("D:D").Hidden = False * * * * * * ChBx1.BackColor = RGB(245, 30, 5) * * * * * * 'ChBx1.ForeColor = * * * * * * ChBx1.Caption = "Standard" *End If End Sub regards FSt1- Hide quoted text - - Show quoted text - I'm not quite sure what you want because you can only hide entire columns, not columns in a row. This code will color the cell yellow and hide columns J:K if anything is inputed into the cell. Empty the cell for no color and unhide. No checkbox overhead needed. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("b2:b22")) Is Nothing _ Or Target.Count 1 Then Exit Sub With Target If Len(Application.Trim(Target)) < 1 Then * .Interior.ColorIndex = 0 Columns("j:k").Hidden = False Else * .Interior.ColorIndex = 6 Columns("j:k").Hidden = True End If End With End Sub- Hide quoted text - - Show quoted text - thanks for your answer, well I will explain it better, I know how to hide a column if I select the checkbox needed and show the column if the checkbox is not selected, the thing is I don't know how to a apply that code to all the checkboxes in my sheet, that's why I used first the automatic creation of checkboxes but now I am stuck because I don't know how to insert the condition of hiding or not. (I want to hide the whole columns K:M). best, Diana |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
On Jun 20, 10:44*pm, FSt1 wrote:
On 20 June, 23:04, Dmhs wrote: I would like to create automatically a checkbox per row but also if the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() * * On Error Resume Next * * Dim c As Range, myRange As Range * * Set myRange = Selection * * For Each c In myRange.Cells * * * * ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select * * * * * * With Selection * * * * * * * * .LinkedCell = c.Address * * * * * * * * .Characters.Text = "" * * * * * * * * .Name = c.Address * * * * * * End With * * * * * * c.Select * * * * * * * * With Selection * * * * * * * * * * .FormatConditions.Delete * * * * * * * * * * .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE" * * * * * * * * * * .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .Font.ColorIndex = 2 'cell background color = White * * * * * * * * * * *'I think here should be the hiding option but i don't know how to write it * * * * * * * * End With * * * * Next * * * * *myRange.Select End Sub regards, DIANA hi personally i don't think you can add that code to the check box in the way you want. to hide and unhide columns, you would have to use the check box click event to trigger the hide/unhide code to run. the code would have to reside in each check box which would be independent of the code that adds the check boxes. I suppose you could program the vb editor to add the code to each check box but i don't know how to do that. i do know it can be a real pain depending on what you are doing. you might check this site out for more info... http://cpearson.com/excel/vbe.aspx here is code i use for one of our engineering files. but i only have 1 check box. it not only hides/unhides a column, it also changes the color and captions of the check box. make adjustments as needed. Private Sub ChBx1_Click() *If Columns("C:C").Hidden = True Then * * * * Columns("C:C").Hidden = False * * * * ChBx1.BackColor = RGB(0, 0, 255) * * * * ChBx1.ForeColor = RGB(245, 245, 5) * * * * ChBx1.Caption = "Metric" * * * * Columns("D:D").Hidden = True * * * * Else * * * * * * Columns("C:C").Hidden = True * * * * * * Columns("D:D").Hidden = False * * * * * * ChBx1.BackColor = RGB(245, 30, 5) * * * * * * 'ChBx1.ForeColor = * * * * * * ChBx1.Caption = "Standard" *End If End Sub regards FSt1- Hide quoted text - - Show quoted text - Thanks for your help, and yes that's my problem i don't want to go to each checkbox and assign it the macro that hides columns, maybe there is a way so I can write a code for all the checkboxes, or something like that. Let me know if you find a way, Best, Diana |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
On Jun 21, 9:45*pm, Dmhs wrote:
On Jun 20, 10:44*pm, FSt1 wrote: On 20 June, 23:04, Dmhs wrote: I would like to create automatically a checkbox per row but also if the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() * * On Error Resume Next * * Dim c As Range, myRange As Range * * Set myRange = Selection * * For Each c In myRange.Cells * * * * ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select * * * * * * With Selection * * * * * * * * .LinkedCell = c.Address * * * * * * * * .Characters.Text = "" * * * * * * * * .Name = c.Address * * * * * * End With * * * * * * c.Select * * * * * * * * With Selection * * * * * * * * * * .FormatConditions.Delete * * * * * * * * * * .FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE" * * * * * * * * * * .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .FormatConditions(1).Interior..ColorIndex = 6 'change for other color when ticked * * * * * * * * * * .Font.ColorIndex = 2 'cell background color = White * * * * * * * * * * *'I think here should be the hiding option but i don't know how to write it * * * * * * * * End With * * * * Next * * * * *myRange.Select End Sub regards, DIANA hi personally i don't think you can add that code to the check box in the way you want. to hide and unhide columns, you would have to use the check box click event to trigger the hide/unhide code to run. the code would have to reside in each check box which would be independent of the code that adds the check boxes. I suppose you could program the vb editor to add the code to each check box but i don't know how to do that. i do know it can be a real pain depending on what you are doing. you might check this site out for more info... http://cpearson.com/excel/vbe.aspx here is code i use for one of our engineering files. but i only have 1 check box. it not only hides/unhides a column, it also changes the color and captions of the check box. make adjustments as needed. Private Sub ChBx1_Click() *If Columns("C:C").Hidden = True Then * * * * Columns("C:C").Hidden = False * * * * ChBx1.BackColor = RGB(0, 0, 255) * * * * ChBx1.ForeColor = RGB(245, 245, 5) * * * * ChBx1.Caption = "Metric" * * * * Columns("D:D").Hidden = True * * * * Else * * * * * * Columns("C:C").Hidden = True * * * * * * Columns("D:D").Hidden = False * * * * * * ChBx1.BackColor = RGB(245, 30, 5) * * * * * * 'ChBx1.ForeColor = * * * * * * ChBx1.Caption = "Standard" *End If End Sub regards FSt1- Hide quoted text - - Show quoted text - Thanks for your help, and yes that's my problem i don't want to go to each checkbox and assign it the *macro that hides columns, maybe there is a way so I can write a code for all the checkboxes, or something like that. Let me know if you find a way, Best, Diana- Hide quoted text - - Show quoted text - You wanted to add check boxes as well as code for the added check boxes right? You have to use VB IDE. Requires digital signature setup too. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro for checkboxes
This may get you closer.
Option Explicit Sub add_checkbox() Dim c As Range Dim myRange As Range Dim CBX As CheckBox Set myRange = Selection For Each c In myRange.Cells Set CBX = ActiveSheet.CheckBoxes.Add _ (c.Left, c.Top, c.Width, c.Height) With CBX .LinkedCell = c.Address .Caption = "" 'don't use names that look like addresses! .Name = "CBX_" & c.Address(0, 0) .OnAction = "'" & ThisWorkbook.Name & "'!testme" End With Next c End Sub Sub testme() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "Checked!" & vbLf _ & CBX.Name & vbLf _ & CBX.TopLeftCell.Address Else MsgBox "not checked" End If End Sub On 06/20/2010 22:04, Dmhs wrote: I would like to create automatically a checkbox per row but also if the checkbox is selected I want to hide columns J to K, I got this code but I don't know how to change it to put the condition about hiding the columns. could anybody help me... this is the code : Sub add_checkbox() On Error Resume Next Dim c As Range, myRange As Range Set myRange = Selection For Each c In myRange.Cells ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select With Selection .LinkedCell = c.Address .Characters.Text = "" .Name = c.Address End With c.Select With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="="& c.Address& "=TRUE" .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked .Font.ColorIndex = 2 'cell background color = White 'I think here should be the hiding option but i don't know how to write it End With Next myRange.Select End Sub regards, DIANA -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with macro (checkboxes) | Excel Programming | |||
Checkboxes disappear after macro is run | Excel Programming | |||
Macro to clear checkboxes | Excel Worksheet Functions | |||
Multiple checkboxes, one macro? | Excel Programming | |||
Multiple checkboxes 1 macro | Excel Programming |