Check Box Macro

ApolloID

Well-known Member
Joined
Jun 8, 2010
Messages
769
Hi, i need help with a macro that will run when a check box in sheet 1 is checked.

I have a check box (2) placed in "A9" and the linked cell is "B10"
I also have a check box (3) in "A11" and linked cell is in "B12"

I need a macro that will change the linked cell (row) height to 120.

If i click check box 2, then to adjust the height of the correspondant cell (row).

If i click check box 3, then to adjust the height of the correspondant cell (row).

Can this be done?

Thanks in advance!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Why not dump the checkboxes for a Doubleclick??
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Row
   Case 10, 12
      If Target.RowHeight <> 120 Then
        Target.RowHeight = 120
      Else:  Target.RowHeight = 15
      End If
   Case Else:
End Select
End Sub

lenze
 
Upvote 0
Wow, so great!!!!! Thanks alot!

It would be great if the rows that will expand will be the rows with some text in A column.
Let's say, now, in A10 and A12 i have the word "Note". If A14 is empty but i have the word "Note" in A19, then the next row to expand to be A19.
I will need this if in the future i will have to insert some photo or some Observations linked to a certain note.

Can this be done?

Thanks alot!!!!

Thanks!
 
Upvote 0
Do you mean something like this???

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Cells(Target.Row,"A") <> "" Then 
         If Target.RowHeight <> 120 Then
         Target.RowHeight = 120
         Else:  Target.RowHeight = 15
         End If
End If
End Sub

lenze
 
Upvote 0
Yes something like this, but this code only works for only one entry in A column.

If i put a word in A column, then that row doesn't change height on doubleclick. The below one does.
This should be ok, but if i put the same word again, a few rows below, also in column A, the code ignore that word. In fact, the code ignore all words in A column except for the first one.
 
Upvote 0
Maybe I'm confused!!
The code I wrote works like this!!
1) When any cell is DoubleClicked, It checks Column "A" in that row for an entry.
2) If Column "A" in that row is Not blank, then it checks the height of the cell DoubleClicked
3) If the height is not equal to 120, then it sets the height to 120
4) If the height is 120, the it sets it to 15

Is that not what you want??
Or perhaps you simply want to loop through Column "A" for Values and when found, set the height to 120, else 15.

lenze
 
Upvote 0
Hi, I need the first code to work only if in column "A" same row, is an word ("Note").
If the cell in column "A" is empty, then the macro to do nothing.

Ex:
If i click row 10 and in A10 i have "Note", then the macro to run as you made it ( you made it perfect )
If i click row 12 and A12 is empty, then the macro to do nothing.
 
Upvote 0
I think my second code does that!! But maybe you want something like this??
Rich (BB code):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Row
   Case 10, 12
      If Cells(Target.Row,"A") <> "Note" Then Exit Sub
      If Target.RowHeight <> 120 Then
        Target.RowHeight = 120
      Else:  Target.RowHeight = 15
      End If
   Case Else:
End Select
End If

lenze
 
Upvote 0
Yes, this is working. It's what i need.
Just that i want to remove "case 10, 12".

I need the code to look in Column "A" for the word "Note" so the code to know in which row to run.

Basically it will replace "case 10, 12".

Can this be done?
 
Upvote 0
The code in post #4 does that!!!
Here is another way!
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
[COLOR=blue]If Cells(Target.Row,"A") <> "Note" Then Exit Sub[/COLOR]
If Target.RowHeight <> 120 Then
    Target.RowHeight = 120
Else: Target.RowHeight = 15
End If
End Sub
 
lenze
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top