Help with Excel VBA

[Nanoman]

Hi there,

I require some help please. Basically I need a range of cells to be selected when I press a tick box (these cells will be copied into another spreadsheet when a macro is activated). The thing is the range it needs to select is relative to where the tick box is.

Sub CheckBox4_Click()
    Dim cBox As CheckBox
    Dim LRow As Integer
    Dim LRange As String
    Dim cell As Range
    Dim rRow As Integer
 
 
Application.ScreenUpdating = False
    LName = Application.Caller
    Set cBox = ActiveSheet.CheckBoxes(LName)
    'Find row that checkbox resides in
    LRow = cBox.TopLeftCell.Row
    LRange = "I" & CStr(LRow)
 
    'Change date in column I, if checkbox is checked
    If cBox.Value > 0 Then
        ActiveSheet.Range(LRange).Value = Date
    'Clear date in column I, if checkbox is unchecked
    Else
        ActiveSheet.Range(LRange).Value = Null
    End If
 
End Sub

This is currently what I have set up. Say for example if the tickbox is selected and the date is displayed in "I7". Then I need it to select the range "B7:I7". It's just the relative thing that I'm stuck on because obviously if the tickbox was in cell "G25" it would still highlight "B7:I7" and not "B25:I25".

Hope that made sense lol.

 

[SimonCHere]

Not sure I really understand but why wouldn't this work? You've done all the hard work.

If cBox.Value > 0 Then
        ActiveSheet.Range(LRange).Value = Date
[COLOR="Red"]        ActiveSheet.Range("B" & CStr(LRow), "I" & CStr(LRow)).Select[/COLOR]
    'Clear date in column I, if checkbox is unchecked
    Else
        ActiveSheet.Range(LRange).Value = Null
    End If

Just add the line I've highlighted.

 

[drak3]

.Offset(x, y)

For example, Range("A1").Offset(0,1).Address will give you B1, and Range("A1").Offset(1,0).Address will give you A2.

However, in VBA try to avoid absolute cell references. Instead, define a named range. For example, define "tick_box" as a named range and set it to refer to where the tick box is and then use Range("tick_box").Offset(x, y).Address to move y columns and x rows relative to it.

You then change the cell "tick_box" refers to, and the code still works.