Calc: Messagebox bei leerer Zelle, ansonsten gehe zu URL

[Woatze]

Hallo!
Ich habe bisher in meiner Calc-Datei Schaltflächen mit der Eigenschaft Aktion: Dokument/Webseite öffnen hinterlegt. Klickt man die Schaltfläche an, springt der Cursor zu einem best. Bereich/Tabellenblatt im Dokument.
Jetzt möchte ich gerne prüfen, ob in der Zelle B2 des Tabellenblattes 3 ein Wert ist oder ob die Zelle leer ist. Bei leerer Zelle soll eine Messagebox mit Hinweistext erscheinen. Ist die Zelle befüllt, soll wie bisher die URL geöffnet werden.

Danke,
Woatze

[komma4]

Ist die Zelle befüllt, soll wie bisher die URL geöffnet werden.

Ich weiss nicht, was Du hier erwartest...

In diesem Forum helfen Anwendern anderen Anwendern bei Fragen.
Hier gibt es keinen kostenfreien StarBasic-Auftragsdienst!


Erarbeite Dir die Grundlagen der StarBasic-Programmierung, mit Hilfe

der StarBasic FAQ http://www.dannenhoefer.de/faqstarbasic/index.html

von Andrews Makro-Dokument

und XRAY

[Woatze]

Hallo Winfried,
danke für den Hinweis. Ich werd es in Zukunft beherzigen. Wenn ich kann, helf ich gern. Aber leider bin ich (noch) Anfänger auf dem Gebiet. Ich hoffe, das wird sich bald ändern. Man lernt doch einiges wenn die ersten Hürden mal genommen sind und man immer weiter in diese Materie einsteigt.
Hier nun meine Lösung, wie es im Moment aussieht:

Code: Alles auswählen

Sub Link12
	oCalc = thiscomponent
	oSheet = oCalc.sheets(2)
	if oSheet.getCellRangeByName("B2").String = "" then
	Hinweis_Laden
	else
	ThisComponent.CurrentController.SetActiveSheet(ThisComponent.Sheets.getByName("12"))
	end if
End Sub

Mit Hinweis_Laden wird eine andere Funktion (eine Messagebox) aufgerufen.
Tut jetzt das, was ich eigentlich wollte.

Grüße
Woatze

[Jörg]

Hallo,
ich habe das o.g. Makro ubernommen und es über Tabellenereignis zugewiesen.
Mein Problem:
Die msgbox öffnet nun bei jeder Änderung. Ich möchte jedoch, daß sich die msgbox nur bei Änderung in der Zelle K8 öffnet.

Code: Alles auswählen

Sub K8
oCalc = thiscomponent
   oSheet = oCalc.sheets(3)
   if oSheet.getCellRangeByName("K8").String = "" then
  msgbox "Kunde abgerechnet?"
   else
   ThisComponent.CurrentController.SetActiveSheet(ThisComponent.Sheets.getByName("12"))
   end if
End Sub

sheets(3) ist o.k. ist das Tabellenblatt
.getByName("12") hab ich erstmal so übernommen, weil ich nicht weiß, was dieser Schritt bewirkt.

Vielen Dank schon mal.
Gruß Jörg

[Karolus]

Hallo

Code: Alles auswählen

sub k8_changed(event)
on Error resume next
if right(event.AbsoluteName, 4) = "$K$8" then
    msgbox "hallo Jörg"
end if
end sub

Karolus

[Jörg]

Hallo Karolus,
funktioniert klasse. Bist ´nen Guter
Vielen Dank!!

[Jörg]

Hallo,
irgendwie ist da jetzt der Wurm drin, oder ich hab´s anfangs übersehen.
Wenn ich eine neue Eingabe in K8 mache, erscheint wie gewollt, die msgbox. Bei Klick auf ok bzw. Enter werden alle Werte in den Zellen D8:I8 sowie C10:D10 gelöscht.
Durch Rückgängig: Eingabe kann das wiederhergestellt werden.
In meiner Testtabelle hab ich das Makro und das Tabellenereignis gelöscht und trotzdem, nach Eingabe und Enter wie oben.

Wie ist das möglich?
Unten mal alle meine Makros.

Code: Alles auswählen

sub RechnungErstellen
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$37:$C$70"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 5

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVD"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = true
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$K$8"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())


end sub

sub Abrechnung
rem ----------------------------------------------------------------------
antwort = msgbox("Abr.-Monat eingetragen?", 33)
if antwort = 2 Then
		 exit sub
end if		
rem define variables

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$F$37:$F$47"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVD"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = true
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())
 
rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$K$8"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

end sub

sub ManuelleWerte
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$I$37:$I$45"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 4

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SV"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = true
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())


end sub


sub PDFspeichern
rem ----------------------------------------------------------------------
sTabName = Thiscomponent.currentcontroller.activesheet.name
msgbox sTabName

rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///E:/openoffice/Monatsabrechnungen/2014/" & sTabName & ".pdf"
args1(1).Name = "FilterName"
args1(1).Value = "calc_pdf_Export"
args1(2).Name = "FilterData"
args1(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EmbedStandardFonts",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Selection",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Sel"
args3(0).Value = true

dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A$3:$L$129"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem------------------------------------------------------------------

dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$A$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())


End Sub



sub Statistik
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$F$46:$F$58"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 12

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVT"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = true
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args8())


end sub


sub neuerKunde
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$E$8:$E$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVD"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = true
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$E$9"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$E$9:$E$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Flags"
args10(0).Value = "SVD"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "$E$9"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())


end sub

sub k8_changed(event)
on Error resume next
if right(event.AbsoluteName, 4) = "$K$8" then
    msgbox "Patient abgerechnet?"
end if
end sub


sub Rechnungansehen
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")


rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 12

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args2())


end sub


sub zurMaske
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())


end sub

[Karolus]

Hallo

Und aus dem Gewurstel sollen wir nun erraten was, warum nicht funktioniert wie du möchtest ( wobei noch nichtmal klar ist was du möchtest ) ??

Karolus

[Jörg]

Hallo,
Erstmal wozu die msgbox: K8 befindet sich in der Eingabemaske Abr._Rech.. Die box soll darauf aufmerksam machen, ob der zuvor eingegebene Datensatz in die Tabelle übergeben wurde.
Insofern Ziel erreicht.
Mich interessiert, warum die Werte in den genannten Zellen verschwinden. In Deinem Makro steht nichts von löschen.
Aber es kann wirklich sein, dass es mir bisher nicht aufgefallen war.
Ich hänge mal die Datei ran. Möglicherweise ist die Erklärung sehr banal.

[Jörg]

Hallo nochmal,
ist eigentlich logisch, dass die Werte verschwinden, da die svweis-Formeln in den genannten Zellen
bei der Eingabe eines neuen Wertes kein Ergebnis liefern können
und deshalb die voherigen Werte "verschwinden".
Sorry für die Aufregung.

Jörg

[Karolus]

Hallo

Ändere mal um in

Code: Alles auswählen

sub k8_changed(event)
on Error goto label
if right(event.AbsoluteName, 4) = "$K$8" then
    msgbox "Patient abgerechnet?"
end if
label: exit sub

End Sub

Ansonsten könntest du dir mal anschauen worin sich die drei subs Rechnungansehen, zurPreisliste und zurMaske eigentlich unterscheiden -- danach löscht du die drei Dinger und ersetzt die aufrufenden Schaltflächen im Dokument durch →Einfügen→Hyperlink....

Karolus

[Jörg]

Hallo Karolus,
Danke. Hab Deinen Tip mit den Hyperlinks umgesetzt.
Ist viel besser als mit Makro.
Dein Makro hab ich auch eingefügt.
Was genau bewirken die Änderungen?

Gruß Jörg

[Jörg]

Hallo, komme leider nicht weiter mit nachfolgendem Code:

Code: Alles auswählen

sub k8_changed(event)
            osheet = thiscomponent.sheets.getbyname("Eingabemaske Abr_Rechnung")
        oRange = osheet.getcellrangebyname("K8")
        if oRange.queryIntersection(event.RangeAddress).count = 1 then   
antwort =  msgbox ("Rechnung erstellt? Verordnung abgerechnet?",36,"Neue Verordnung?")
if antwort = 7 then 
[color=#FF0000]hier müßte jetzt eigentlich hinein, daß eine weitere eingabe nicht möglich ist. So im Sinne "exit sub"
Dann bekomme ich allerdings die Fehlermeldung sub innerhalb einer Prozedur unzulässig.[/color]
		end if    
        end sub

Würd mich freuen, wenn mir jemand auf die Sprünge hilft.
Gruß Jörg