Excel

Kalenderwoche

• http://www.herber.de/forum/archiv/476to480/478449_Excelfunktion_in_VBA.html

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""" & _
   ")"