Difference between revisions of "Excel"
From Blue-IT.org Wiki
(→Checkbox) |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 6: | Line 6: | ||
=== DatePicker === | === DatePicker === | ||
| + | |||
| + | First of all make shure the Datepicker will not be printed! This way you can put it directly above the fieldit should refert to. | ||
==== Checkbox ==== | ==== Checkbox ==== | ||
| + | If the checkbox of the Datepicker will be unchecked, the value of the field gets "#NV". | ||
| + | |||
| + | This can be tested with (... where "DATA!C20" is the cell on the sheet you like to refer to): | ||
| + | |||
| + | * if your intend is to alter a text based on this value | ||
| + | =WENN(ISTNV(DATA!C20);"";"bis") | ||
| + | |||
| + | * if your intend ist setting the date of the datepicker in another sheet - or keep the field empty: | ||
| + | =WENN(ISTNV(DATA!C20);"";WERT(DATA!C20)) | ||
| + | |||
| + | * to blank a cell use the following code: | ||
Private Sub DTPicker4_OnClick() | Private Sub DTPicker4_OnClick() | ||
Latest revision as of 08:20, 6 December 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
First of all make shure the Datepicker will not be printed! This way you can put it directly above the fieldit should refert to.
Checkbox
If the checkbox of the Datepicker will be unchecked, the value of the field gets "#NV".
This can be tested with (... where "DATA!C20" is the cell on the sheet you like to refer to):
- if your intend is to alter a text based on this value
=WENN(ISTNV(DATA!C20);"";"bis")
- if your intend ist setting the date of the datepicker in another sheet - or keep the field empty:
=WENN(ISTNV(DATA!C20);"";WERT(DATA!C20))
- to blank a cell use the following code:
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""" & _
")"