Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen

Abfrage auf Parameter | Herbers Excel-Forum


Betrifft: Abfrage auf Parameter von: jo_cindy
Geschrieben am: 03.02.2010 19:06:34

Hi Leute,

habe mir mit eurer Hilfe ein Makro "gestrickt". Daten aus einer Zeiterfassung werden auf das Wesentliche reduziert und aufbereitet, u.a. erfolgt die Berechnung der Lohnleistung inklusive Zuschläge. Nun habe ich den Grundlohn fest "eingebaut". Wenn dieser sich ändert, muss ich das Makro ändern. Wesentlich besser wäre es natürlich, wenn eine Abfrage beim Starten des Makros kurz fragt, ob die eingestellten Parameter gültig sind. Ich stelle mir das in einer Art pop up vor. In diesem Fenster erscheinen die Lohnarten und die Frage, ob diese korrekt sind. Bei Bedarf, kann man diese anpassen, wenn nicht per Klick auf OK Makro weiter ausführen. Innerhalb des Makros gibt es keinen Textbezug zu den Konditionen, diese sollten dann in dem Fenster fix sein:

Beispiel: Qualifikation A ín Euro 14,05
Qualifikation B in Euro 14,40

In dem fett markierten Bereich befinden sich die Parameter. Gibt es eine Funktion ähnlich wie suchen/ändern, also wenn bei Qualifikation A in Euro 14,50 eingetragen wird, dann ändere alle Werte in der Formel von 14,05 auf 14,50?

Weiß jemand wie das geht, bzw. ob das geht?

Anbei mal das Makro:

Range("A1:AZ10000").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").EntireColumn.AutoFit
Range("D1:E10000").Select
Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1:C10000").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Range("C1:E10000").Select
Columns("C:C").EntireColumn.AutoFit
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").EntireColumn.AutoFit
Columns("G:M").Select
Selection.EntireColumn.Hidden = True
Columns("O:Q").Select
Selection.EntireColumn.Hidden = True
Columns("T:Y").Select
Selection.EntireColumn.Hidden = True
Range("z2:AB10000").Select
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Service Logistik"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Logistik"
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Kasse"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "Kasse Einarb"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Lohnkosten inkl Zuschläge"
Range("AC1:AG10000").Select
Selection.NumberFormat = "#,##0.00 $"
Range([AC2], [AC2].End(xlDown)).Offset(0, 0).FormulaR1C1 = "=IF(RC[-25]=10,14.05*RC[-11]+(RC[-3]*0.25*14.05)+(RC[-2]*0.25*14.05)+(RC[-1]*0.5*14.05),0)"
Range([AD2], [AD2].End(xlDown)).Offset(0, 0).FormulaR1C1 = "=IF(RC[-26]=20,14.05*RC[-12]+(RC[-4]*0.25*14.05)+(RC[-3]*0.25*14.05)+(RC[-2]*0.5*14.05),0)"
Range([AE2], [AE2].End(xlDown)).Offset(0, 0).FormulaR1C1 = "=IF(RC[-27]=30,14.4*RC[-13]+(RC[-5]*0.25*14.4)+(RC[-4]*0.25*14.4)+(RC[-3]*0.5*14.4),0)"
Range([AF2], [AF2].End(xlDown)).Offset(0, 0).FormulaR1C1 = "=IF(RC[-28]=40,14.05*RC[-14]+(RC[-6]*0.25*14.05)+(RC[-5]*0.25*14.05)+(RC[-4]*0.5*14.05),0)"

Range([AG2], [AG2].End(xlDown)).Offset(0, 0).FormulaR1C1 = "=RC[-4]+RC[-3]+RC[-2]+RC[-1]"
Columns("AC:AG").Select
Columns("AC:AG").EntireColumn.AutoFit
ActiveWindow.DisplayZeros = False
Range("A1:AG1").Select
Range("AG1").Activate
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("AG1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("Z:AB").Select
Range("AB1").Activate
Selection.EntireColumn.Hidden = True
Range("A1").Select
Columns("AH:AH").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete shift:=xlToLeft
Range("AG2").Select
End
Dim z As Long, lZ As Long
lZ = Sheets("currentregion").Cells(65536, 2).End(xlUp).Row
For z = lZ To 1 Step -1
With Sheets("currentregion")
If .Cells(z, 1) = "" Then .Rows(z).Delete
End With
Next
End Sub

  

Betrifft: AW: Abfrage auf Parameter von: Ramses
Geschrieben am: 03.02.2010 19:43:58

Hallo

Sorry, aber wozu hier ein Makro, das zudem Fehleranfällig ist.

Schreib doch den grundlohn in eine extra Tabelle und frag den Wert einfach ab.
Dann musst du den Grundlohn bloss in einer einzigen Zelle ändern.

Gruss Rainer


  

Betrifft: AW: Abfrage auf Parameter von: jo_cindy
Geschrieben am: 03.02.2010 19:51:01

Hi Ramses,

also die Werte durch einen Bezug auf ein Feld ändern, o.k. z.b. steht in dem Feld A1 auf Reiter "Parameter" der Lohnbezug 14,05 Euro für Qualifikation A und anstelle der 14,05 Euro in der Formel füge ich den Bezug auf die Zelle A1 des Reiters Parameter - richtig? Und wie frage ich ab, ob die Werte geändert werden müssen? Und wie ändert der User dann den Wert? Der User (davon muss ich ausgehen), kann diesen Wert nicht im Makro oder in der Tabelle ändern. Das Makro wird als xlam versendet und in die Menüleiste eingebunden. Der User hat also keinen Zugriff auf die "Grundtabelle"


  

Betrifft: AW: Abfrage auf Parameter von: Ramses
Geschrieben am: 04.02.2010 11:18:17

Hallo

ich meine das sol

Tabelle1

 ABCDEFGHIJ
1QualiLohnStundenTotal  QualiLohn  
2Qualifikation A14.05342.15  Qualifikation A14.05  
3Qualifikation B14.5458  Qualifikation B14.5  
4          
5      Steht auf einer versteckten/ausgeblendeten   
6      Tabelle deiner Mappe wo der User keinen   
7      Zugriff darauf hat.   
8          
9          
10          
11          

Formeln der Tabelle
ZelleFormel
B2=SVERWEIS(A2;$G$2:$H$3;2;0)
D2=C2*B2
B3=SVERWEIS(A3;$G$2:$H$3;2;0)
D3=C3*B3


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Mit deinem Makro kannst du die Were aus dieser geschützten Tabelle auslesen und zur Anzeige bringen, allenfalls dann auch ändern, und zwar nur an dieser Stelle.
Alle Formeln in deiner Mappe sind damit wieder aktuell ohne dort etwas ändern zu müssen

Gruss Rainer


  

Betrifft: AW: Abfrage auf Parameter von: jo_cindy
Geschrieben am: 04.02.2010 12:52:35

Hi Rainer,

wenn ich dich richtig verstehe, dann muss eine Änderung der Werte für Zelle B2 in H2 vorgenommen werden, richtig? Und diese Änderung kann ich doch dann nur in der "versteckten" Tabelle durchführen, oder? Es soll aber jeder, der mit dem Makro arbeitet, die Werte ändern können. Ich hatte mir eben eine Abfrage vorgestellt, wenn das Makro gestartet wird, so nach dem Motto: "Bleiben die Werte erhalten", wenn ja, dann auf "o.K" klicken, wenn nein, bitte ändern und anschließend auf "O.K." klicken.


  

Betrifft: AW: Abfrage auf Parameter von: Ramses
Geschrieben am: 04.02.2010 13:36:12

Hallo

Wenn du mit Makros arbeiten willst, solltest du zumindest die Grundfunktionen eines Makros beherrschen.
Was hindert dich daran die Zellen in der versteckten Tabelle auszlesen, dem User zur Anzeige zu bringen und bei Bedarf wieder zu ändern.
das ist doch kein Problem.

Gruss Rainer


  

Betrifft: AW: Abfrage auf Parameter von: jo_cindy
Geschrieben am: 04.02.2010 14:06:12

Hi Rainer,

vielleicht reden/schreiben wir einfach aneinander vorbei und ja, vielleicht beherrsche ich keine Makros, aber ich versuche mich wenigstens daran und bisher für meine Zwecke auch erfolgreich. Mein Makro funktioniert ja, nur eben nicht so "komfortabel" wie ich es gerne hätte. Du schreibst bei Bedarf wieder ändern.... meinst du so wie ich geschrieben hatte, also ich muss es in der "versteckten" Tabelle ändern? Also reingehen, ändern, sichern und dann das Makro mit den neuen Parameter wieder an die User mailen???


  

Betrifft: Wir reden aneinander vorbei.... von: Ramses
Geschrieben am: 04.02.2010 14:28:13

Hallo

Definiere doch einfach mal, was du GRUNDSÄTLICH machen willst.

"...und dann das Makro mit den neuen Parameter wieder an die User mailen..."

Wieso, das machst du doch jetzt auch nicht.
In deinen jetzigen Makros ist doch die komplette Formel mit den neuen Sätzen auch schon drin ??

Mal als Grundsatz:

Private Sub Workbook_Open()
dim Qe as Long
Dim qA as Double, qB as Double
Qe = Msgbox("Die aktuellen Stundenansätze lauten:" & vbcrl _
"Qualifikation A: " & Worksheets("Deine Tabelle mit den Werten").Range("Zelle mit Wert QA") &  _
vbcrlf _
"Qualifkation B: " & Worksheets("Deine Tabelle mit den Werten").Range("Zelle mit Wert QB") &  _
vbcrlf _
"Möchten Sie die Werte änbdern ? ", vbcYesNo+VbQuestion,"Stundenansätze")
If Qe = vbNo then Exit Sub
qA = CDbl(Application.InputBox("Stundensatz für QA mit Punkt als Trennzeichen eingeben", " _
Qualifikation A", "14.05", Type:=1))
qB = cdbl(Application.InputBox("Stundensatz für QB mit Punkt als Trennzeichen eingeben", " _
Qualifikation B", "14.50", Type:=1))
Worksheets("Deine Tabelle mit den Werten").Range("Zelle mit Wert QA") = qA
Worksheets("Deine Tabelle mit den Werten").Range("Zelle mit Wert QB") = qB
End Sub
Das ist doch die ganze Hexerei !?

Gruss Rainer


  

Betrifft: AW: Wir reden aneinander vorbei.... von: jo_cindy
Geschrieben am: 04.02.2010 15:09:02

Hi Rainer,

erst einmal vielen Dank für deine Geduld. Am 3.2 hatte ich geschrieben, dass ich das Makro als AddIn (xlam) versende. Das Makro wird von ca. 30 Usern eingesetzt. Wenn wir jetzt eine Lohnanpassung bekommen, möchte ich natürlich nicht das Makro ändern, versenden und darauf verlassen, dass jeder User auf seinem Desktop das aktuelle Makro pflegt/einbindet. Deshalb dachte ich, dass es besser wäre, wenn ein Fenster beim Starten aufgeht und eine Bestätigung der Parameter verlangt, bzw. eine Anpassung zuläst, welche gespeichert wird.


  

Betrifft: Ich denke wir sollten hier Schluss machen... von: Ramses
Geschrieben am: 04.02.2010 15:22:15

Hallo

"...Wenn wir jetzt eine Lohnanpassung bekommen, möchte ich natürlich nicht das Makro ändern, versenden und darauf verlassen, dass jeder User auf seinem Desktop das aktuelle Makro pflegt/einbindet..."
Und was ist mit dem AddIn ? Wie willst du das denn sicherstellen ?
Hat jeder User das Recht vertrauenswürdige Pfade zu definieren, bzw. Zugriff auf die AddIn Verzeichnisse ?

"...Deshalb dachte ich, dass es besser wäre, wenn ein Fenster beim Starten aufgeht und eine Bestätigung der Parameter verlangt, bzw. eine Anpassung zuläst, welche gespeichert wird...."

Das Makro das ich dir oben gezeigt habe, macht genau das.
Du musst nur noch die Tabellennamen und die Bereiche anpassen.

Ich werde mich hier ausklinken da ich keine weitere Inputs zu diesem Thema habe.

Gruss Rainer


Beiträge aus den Excel-Beispielen zum Thema "Abfrage auf Parameter"