Trying to learn VBA

davonovic

New Member
Joined
Mar 28, 2013
Messages
5
My question is a bit complicated. Let's say I want to have cell B1 be checked automatically for a value and then display a string in D1 cell. But this should only occur if the adjacent check box in C1 is not checked. Currently, I have it to where the check box prompts the action. But it doesn't do it automatically. When i enter the number in B1, nothing happens. The desired action only occurs when I actively check or uncheck a box. Is it possible to check the cell without actuating the checkbox? Please help!!!

Code:
 Sub Check_Cell(CRow As Integer, CCol As Integer)

If Cells(CRow, "B") > 5 And Cells(CRow, "B") < 30 Then
Application.EnableEvents = False
Cells(CRow, B) = "Not Complete"
Application.EnableEvents = True

Else
Application.EnableEvents = False
Cells(CRow, B) = Null
Application.EnableEvents = True

End If

End Sub

Sub Process_CheckBox(pObject)

Dim LRow As Integer
Dim LRange As String

'Find location that checkbox resides in
LRow = pObject.TopLeftCell.Row
LCol = pObject.TopLeftCell.Column

LRange = "D" & CStr(LRow)

'Change date in column D, if checkbox is checked
If pObject.Value = True Then
ActiveCell.Font.Color = RGB(0, 0, 0)
ActiveSheet.Range(LRange).Value = Date

'Clear date and insert string in column D, if checkbox is unchecked
Else
Call Check_Cell(LRow, LCol + 1)

End If

End Sub

Private Sub CheckBox1_Click()
Process_CheckBox CheckBox1
End Sub
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
davonavic,

I can't be certain that I've interpreted what you want correctly and I don't know the number of check boxes you are dealing with so this may or may not help.

For each Check Box, right click & format, then set the Linked cell by pointing to the cell in which it is placed.
This will mean that True or False will show in that cell dependant upon the box being ticked or not. You can format the text in these cells, individually or the whole range, to be 'white' so that it is invisible.
Testing the value of that cell, with code, will tell you if it's related check box is ticked or not, without having to determine the check box name.

You will need to code for the Worksheet_Change event similar to below. Edit the range, for column B, to suit.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if changed cell is range B1:B10 *** edit to suit
If Intersect(Target, Range("B1:B10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
'Check if col C value is True or False
'NB Col C cells are the linked cells of their respective check boxes
'If True
    If Target.Offset(0, 1) = True Then
    Target.Offset(0, 2) = Date
    Else
'If False
            If Target > 5 And Target < 30 Then
            Target = "Not Complete"
            Else
            Target = Null
            End If
    End If
Application.EnableEvents = True
End Sub


I hope that makes sense and helps.
 
Upvote 0
First of all, thank you for responding. My question is a bit convoluted in the first place. So here is my situation.
1)I have a check box set up where if I check it, it spits out the date of the change.
 
Upvote 0
First of all, thank you for responding. My question is a bit convoluted in the first place. So here is my situation. </SPAN>

My desire:</SPAN>
1) I have a checkbox in Col “E” set up where if I select it, it displays the date of the change in the adjacent cell (Col “F”). </SPAN>
2) I want the cells in Col “F” to check the value in Col “D” in the same row and display a string (e.g. “Not Complete”) if it falls within a certain value range.</SPAN>
i) I want the string to be displayed automatically if the checkbox is not selected.</SPAN>
ii) If the checkbox is selected, I want the date to override the string in the Col “F” cells</SPAN>

My quandary is 2-fold:</SPAN>
1) I can set up a subroutine for the checkbox and a private subroutine for the Col “F” cells. But the string always overrides the date regardless of the checkbox condition.</SPAN>

2) I can set up 2 subroutines for the checkbox and the Col “F” cells where the cells subroutine is called on the condition that the checkbox is not checked. But the Col “F” cell string is only displayed after I physically select and then unselect the checkbox. </SPAN>

I want the Col “F” to automatically display the string regardless of whether I select the checkbox or not and that once I select the checkbox. The Col “F” cell value stays as the date. I am new to VBA so I don’t know how to do that.
</SPAN>
 
Upvote 0
davonavic,

I can't be certain that I've interpreted what you want correctly and I don't know the number of check boxes you are dealing with so this may or may not help.

For each Check Box, right click & format, then set the Linked cell by pointing to the cell in which it is placed.
This will mean that True or False will show in that cell dependant upon the box being ticked or not. You can format the text in these cells, individually or the whole range, to be 'white' so that it is invisible.
Testing the value of that cell, with code, will tell you if it's related check box is ticked or not, without having to determine the check box name.

You will need to code for the Worksheet_Change event similar to below. Edit the range, for column B, to suit.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if changed cell is range B1:B10 *** edit to suit
If Intersect(Target, Range("B1:B10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
'Check if col C value is True or False
'NB Col C cells are the linked cells of their respective check boxes
'If True
    If Target.Offset(0, 1) = True Then
    Target.Offset(0, 2) = Date
    Else
'If False
            If Target > 5 And Target < 30 Then
            Target = "Not Complete"
            Else
            Target = Null
            End If
    End If
Application.EnableEvents = True
End Sub


I hope that makes sense and helps.

I have over 70 check boxes, is there a way to create a for loop for checking them?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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