Difference between revisions of "Excel"

From Blue-IT.org Wiki

(Checkbox)
Line 8: Line 8:
  
 
==== Checkbox ====
 
==== Checkbox ====
 +
If the checkbox is unchecked, the value of the field get "#NV". This can be prooven with
 +
 +
* for altering a text:
 +
=WENN(ISTNV(STAMMDATEN!C20);"";"bis")
 +
 +
* for getting the date of the datepicker in another sheet:
 +
=WENN(ISTNV(STAMMDATEN!C20);"";WERT(STAMMDATEN!C20))
 +
 +
To blank a cell use the following code:
  
 
  Private Sub DTPicker4_OnClick()
 
  Private Sub DTPicker4_OnClick()

Revision as of 09:59, 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

If the checkbox is unchecked, the value of the field get "#NV". This can be prooven with

  • for altering a text:
=WENN(ISTNV(STAMMDATEN!C20);"";"bis")
  • for getting the date of the datepicker in another sheet:
=WENN(ISTNV(STAMMDATEN!C20);"";WERT(STAMMDATEN!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""" & _
   ")"