Difference between revisions of "Excel"
From Blue-IT.org Wiki
(→Open Street Map Hyperlink in Excel) |
|||
| Line 4: | Line 4: | ||
ActiveCell.FormulaR1C1 = _ | ActiveCell.FormulaR1C1 = _ | ||
"=CONCATENATE(""0"",TRUNC((RC[-25]-DATE(YEAR(RC[-25]+4-WEEKDAY(RC[-25],2)),1,-9+WEEKDAY(RC[-25],3)))/7),"" / "",YEAR(RC[-25]))" | "=CONCATENATE(""0"",TRUNC((RC[-25]-DATE(YEAR(RC[-25]+4-WEEKDAY(RC[-25],2)),1,-9+WEEKDAY(RC[-25],3)))/7),"" / "",YEAR(RC[-25]))" | ||
| + | |||
| + | === DatePicker === | ||
| + | |||
| + | ==== Checkbox ==== | ||
| + | |||
| + | Private Sub DTPicker4_OnClick() | ||
| + | |||
| + | If IsNull(DTPicker4.Value) = False Then | ||
| + | Worksheets("DAT").Cells(nPubRow, 37).Value = DTPicker4.Value | ||
| + | Else | ||
| + | ' If the user deselected the checkbox I should delete the cell content... | ||
| + | Worksheets("DAT").Cells(nPubRow, 37).Delete | ||
| + | End If | ||
| + | |||
| + | End Sub | ||
=== Open Street Map Hyperlink in Excel === | === Open Street Map Hyperlink in Excel === | ||
Revision as of 09:56, 28 November 2014
Kalenderwoche
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""0"",TRUNC((RC[-25]-DATE(YEAR(RC[-25]+4-WEEKDAY(RC[-25],2)),1,-9+WEEKDAY(RC[-25],3)))/7),"" / "",YEAR(RC[-25]))"
DatePicker
Checkbox
Private Sub DTPicker4_OnClick()
If IsNull(DTPicker4.Value) = False Then
Worksheets("DAT").Cells(nPubRow, 37).Value = DTPicker4.Value
Else
' If the user deselected the checkbox I should delete the cell content...
Worksheets("DAT").Cells(nPubRow, 37).Delete
End If
End Sub
Open Street Map Hyperlink in Excel
Koordinates in E10 and F10 like: "48,9283475" (with comma").
Zell formula:
=HYPERLINK("http://www.osm.org/?mlat="&WECHSELN(E10;",";".")&"&mlon="&WECHSELN(F10;",";".")&" & zoom="&E10&"#map=16/"&WECHSELN(RUNDEN(E10;5);",";".")&"/"&WECHSELN(RUNDEN(F10;5);",";".");"Stelle bei OpenStreetMap")
VBS (this is real crazy, but it works !).
; gets , " gets ""
ActiveCell.Formula = "=HYPERLINK(" & _
"""http://www.osm.org" & _
"/?mlat=" & _
""" & WECHSELN(E10,"","",""."")" & _
" & ""/&mlon=" & _
""" & WECHSELN(F10,"","",""."")" & _
" & ""&zoom=""& E10 & ""#map=16/""" & _
" & WECHSELN(RUNDEN(E10,5),"","",""."") & ""/""" & _
" & WECHSELN(RUNDEN(F10,5),"","",""."")" & _
",""Stelle bei OpenStreetMap""" & _
")"