Excel Project - Help Needed - where to start 1

[SkreeM]

Hi All,

I'm creating a spreadsheet using excel 2007 for managing issuing of equipment to our staff, I've got the basic spreadsheet with all the info filled in. What i wanted to do was add a button(or similar) to each row which when clicked marks the equipment as returned. To do this is needs to strikeout the text along the row, add the word YES to column J and the current Date to column K.

I've recoreded a macro of doing it to the first line of my table, but don't know where to go from here. The code means very little to me, i can see the formatting changes and the text being inserted but I don't know how to change the cell references from the existing fixed ones to dynamic for each bit of equipment. Please help

Thanks In Advance

Giles

 

[SkipVought]

Please post your meaningless recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkreeM]

Meaning less code

    Range("C2:I2").Select
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = True
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "Yes"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = True
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "9/30/2008"
    Range("K3").Select
End Sub

I do understand the bits which apply the formatting, as they look self explanatory its the cell selections that are the problem.


Thanks

Skr

 

[SkipVought]

I could not test this fully, not having 2007, so the Theme constants I had to comment out.

Copy this code and paste in the SHEET CODE WINDOW -- right click the sheet tab, select view code and paste.

Making a different selection in column A will run the code for the data in that row.

Sub test(r As Range)
    With r
        With Range(Cells(.Row, "C"), Cells(.Row, "I")).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = True
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
    
        With Cells(.Row, "J")
            .Value = "Yes"
            With .Characters(Start:=1, Length:=3).Font
                .Name = "Calibri"
                .FontStyle = "Regular"
                .Size = 11
                .Strikethrough = True
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = 0
                .ThemeFont = xlThemeFontMinor
            End With
        End With
        With Cells(.Row, "K")
            .Value = "9/30/2008"
        End With
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        test Target
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

You could use conditional Formats to achieve the strikeout depending on the value in J.

To add the date when cell J? changes to "yes" you will need to use the Change Event (look it up in help) to set off some code. Test for the value and if "Yes" apply code like

With Target.offset(0,1)
    .Formula = "=today()"
    .Value = ActiveCell.Value
End with

Of course having detected the change you could apply the formatting as you recorded but delete the many rows of your code that are not needed eg .OutlineFont=False




Gavin

 

[SkreeM]

Thanks, I hadn't thought of doing it using conditional formatting, that would be much easier I think. Will look at change event presumably i could also use it to update hidden cells in a shared workbook with details of the user who "Booked it Back In"

Will try i when i'm back in the office tomorrow.

Thanks

Skr