16. december 2006 - 16:47Der er
15 kommentarer og 1 løsning
Dato skal vises i statuslinjen
Hej
Jeg har et ark hvor jeg i følgende kolonner har følgende data stående i:
A Ugenummer G Timer for mandag i ugenummeret H Timer for tirsdag i ugenummeret I Timer for onsdag i ugenummeret J Timer for torsdag i ugenummeret K Timer for fredag i ugenummeret L Timer for lørdag i ugenummeret M Timer for søndag i ugenummeret
Jeg har ligeledes en celle indeholdende årstallet - celle Tår.
Jeg vil gerne i statuslinjen kunne se hvilken dato jeg står på når jeg eksempelvis står i kolonne J (torsdag i ugen).
Jeg er ikke godt nok hejjem i VBA til at kunne udarbejde funktionen selv, så jeg håber der er en der kan hjælpe.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End Sub
Det ser jo rigtig godt ud men hvordan får jeg den til at virke på alle kolonnerne G-M?
Jeg har forsøgt men det virker ikke:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Not Intersect(Target, Range("G:G")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("H:H")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("I:I")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("J:J")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("K:K")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("L:L")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("M:M")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Not Intersect(Target, Range("G:G")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 1, "dd-mm-yyyy") End If If Not Intersect(Target, Range("H:H")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 2, "dd-mm-yyyy") End If If Not Intersect(Target, Range("I:I")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 3, "dd-mm-yyyy") End If If Not Intersect(Target, Range("J:J")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("K:K")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 5, "dd-mm-yyyy") End If If Not Intersect(Target, Range("L:L")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 6, "dd-mm-yyyy") End If If Not Intersect(Target, Range("M:M")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 7, "dd-mm-yyyy") End If
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Intersect(Target, Range("G:M")) Is Nothing Then Exit Sub Application.StatusBar = Format(DateSerial(Year(Now), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + Target.Column - 4, "dd-mm-yyyy") End Sub
Jeg mangler at få Year(Now) erstattet med Tår, hvilket jeg har forsøgt i nedenstående men det virker ikke.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
TYear = Range("Tår").Value
Application.StatusBar = "" If Not Intersect(Target, Range("G:G")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 1, "dd-mm-yyyy") End If If Not Intersect(Target, Range("H:H")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 2, "dd-mm-yyyy") End If If Not Intersect(Target, Range("I:I")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 3, "dd-mm-yyyy") End If If Not Intersect(Target, Range("J:J")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 4, "dd-mm-yyyy") End If If Not Intersect(Target, Range("K:K")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 5, "dd-mm-yyyy") End If If Not Intersect(Target, Range("L:L")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 6, "dd-mm-yyyy") End If If Not Intersect(Target, Range("M:M")) Is Nothing Then Application.StatusBar = Format(DateSerial(Year(TYear), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + 7, "dd-mm-yyyy") End If
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Intersect(Target, Range("G:M")) Is Nothing Then Exit Sub Application.StatusBar = Format(DateSerial(Year(Tår), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + Target.Column - 4, "dd-mm-yyyy") End Sub
kan du ikke henvise til cellen - ret C1 til din celle ved ikke lige hvordan man fanger et defineret navn
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Intersect(Target, Range("G:M")) Is Nothing Then Exit Sub Application.StatusBar = Format(DateSerial(Range("C1"), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + Target.Column - 6, "dd-mm-yyyy") End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "" If Intersect(Target, Range("G:M")) Is Nothing Then Exit Sub Application.StatusBar = Format(DateSerial(Val(Ark2.Range("Tår")), 1, 1) + ((Cells(Target.Row, 1) - 1) * 7) + Target.Column - 6, "dd-mm-yyyy") End Sub
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
