Nächste ungenutzte Zahl finden / doppelte Zahlen verhindern

[Elex]

Hi

wie schon geschrieben. Nummernvergabe von Hand oder mit VBA. Hier noch mal ein Bsp. mit Bestätigungsabfrage.
Ort eintragen und dann Doppelklick auf den Ort.
Der Code kommt in das Modul Tabelle 1.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Range

If Target.Column = 1 Then
  With Sheets("Tabelle2").Range("a2:a100")
    Set n = .Find(Target.Value, LookIn:=xlValues)
      If Not n Is Nothing Then
         If MsgBox("Soll " & Target.Address & " " & Target.Value & _
            " die Nr. " & n.Offset(, 1) & " zugewiesen werden?", _
           vbYesNo Or vbQuestion, "Zuweisen") = vbYes Then
           Target.Cells.Offset(, 3) = n.Offset(, 1)
         End If
      Else
       MsgBox "Ort nicht gefunden."
      End If
  End With
  Cancel = True
End If

End Sub

Ich weis jetzt nicht wie du das mit der Doppelvergabe für die beiden Orte gelöst hast und ob es jetzt mit der automatischen Zuweisung klappt.
Im Code in der Zeile "With Sheets("Tabelle2").Range("a2:a100")" deinen Blattnamen eintragen wenn er anders lautet bzw. den Bereich anpassen wenn du mehr als 99 Orte verwendest.

Gruß Elex

[Foley]

Hi Elex,

hab deine VBA Lösung gerad in meiner Tabelle verbaut und bin begeistert. Alles klappt tadellos. Ich habe bei  "Target.Cells.Offset(, 3)"die 3 noch zu einer 4 geändert, weil die Nummern in meiner Originaldatei in Spalte E und nicht in D vergeben werden. Da ich leider gar kein VBA kann, ist es immer interessant zu sehen wie es funktioniert. Damit ist der erste Punkt meines Vorhabens erfüllt. ::D Ich gucke gerade ob es möglich ist in der Spalte für die Nummern nur Zahlen zuzulassen, die als nächste freie Zahl ausgegeben wurden. Damit wäre dann auch keine Falscheingaben oder doppelt vergebene Zahlen mehr möglich. Mal sehen. Vielleicht geht das ja mit einer Datenüberprüfung.


Vielen Dank für deine Hilfe und viele Grüße

Foley

[Foley]

Hi nochmal

mir ist aufgefallen, dass durch einen Doppelklick auf den Ort auch dann eine neue Nummer vergeben wird, wenn bereits eine Nummer zugewiesen wurde. Man hat dann als Sicherheit zwar immer noch die Abfrage ob das auch wirklich gemacht werden soll, es wäre aber toll wenn man es so lösen könnte, das nur eine Nummer vergeben wird, wenn die Zelle dazu noch leer ist. Ist das möglich?

Viele Grüße 

Foley

[Elex]

Hi,

zwischen der Zeile     "If Not n Is Nothing Then"    und der    "Else"     den Code.

Code:

     If Not n Is Nothing Then
       If Target.Cells.Offset(, 4) = "" Then
        If MsgBox("Soll " & Target.Address & " " & Target.Value & _
           " die Nr. " & n.Offset(, 1) & " zugewiesen werden?", _
          vbYesNo Or vbQuestion, "Zuweisen") = vbYes Then
          Target.Cells.Offset(, 4) = n.Offset(, 1)
        End If
       End If
     Else

[Foley]

Hi Elex

das klappt mal wieder wie am Schnürchen. Vielen Dank. Ich habe noch versucht das zu Beginn der Blattschutz aufgebhoben wird, dann die Nummer vergeben wird und dann der Blattschutz wieder aktiviert wird. 

 

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:="test"
Dim n As Range

If Target.Column = 1 Then
  With Sheets("Tabelle2").Range("a2:a100")
    Set n = .Find(Target.Value, LookIn:=xlValues)
      If Not n Is Nothing Then
       If Target.Cells.Offset(, 4) = "" Then
        If MsgBox("Soll " & Target.Address & " " & Target.Value & _
           " die Nr. " & n.Offset(, 1) & " zugewiesen werden?", _
          vbYesNo Or vbQuestion, "Zuweisen") = vbYes Then
          Target.Cells.Offset(, 4) = n.Offset(, 1)
        End If
       End If
     Else
       MsgBox "Ort nicht gefunden."
      End If
  End With
  Cancel = True
End If

ActiveSheet.Protect Password:="test"
End Sub

Merkwürdiger Weise verschiebt sich das mit dem Doppelklick dann auf die Spalte mit der zu vergebenden Nummern. Aber so ist es auch schön.
Leider funktioniert mit Blattschutz meine Gruppierung nicht mehr mit der ich einige Spalten ausblenden lasse um mehr Übersicht zu haben und die VBA Lösungen die ich dafür bisher finden konnte, funktionieren bei mir irgendwie nicht. Hast du da vielleicht auch eine Idee für?

Viele Grüße

Foley

P.S.: Problem Gelöst

[Elex]

Hi

Merkwürdiger Weise verschiebt sich das mit dem Doppelklick dann auf die Spalte mit der zu vergebenden Nummern.

Ich habe deinen Code in meine Datei eingefügt. Das Blatt ist geschütz und die restliche Ausführung funktionierte weiterhin wie gewohnt. Somit kann ich leider das Verhalten der Verschiebung nicht nachvollziehen.

Das mit dem setzen und immer wieder aufheben des Blattschutz ist so nicht nötig. Entferne also deine hinzugefügten Zeilen und schreibe den Code in das Modul DieseArbeitsmappe.

Code:

Sub Workbook_Open()
Sheets("Tabelle1").Activate
ActiveSheet.Protect userinterfaceonly:=True, Password:="test"  
ActiveSheet.EnableOutlining = True    'somit sollte Gliederung funktionieren
End Sub

Bis dahin

[Foley]

Hi Elex

ich habe in meiner Tabelle nur die Spalte mit den Nummern als gesperrt formatiert. Vielleicht liegt es daran. Aber wie gesagt, für mich ist es nicht schlimm ob ich nun auf den Ort oder auf das Feld für die Nummer klicken muss. Macht ja beides irgendwie Sinn.

Auf die Lösung zum Filtern und Gruppieren trotz Blattschutz, bin ich schon gekommen. Es wollte nur die ganze Zeit nicht klappen weil ich es nicht bei "Diese Arbeitsmappe" eingefügt habe sondern dort wo der Restliche Code auch ist. Das stand leider nicht überall beschrieben. Danke :)

Dafür bin ich auf ein weiteres Problem gestoßen. Ich gebe in Spalte A einen Ort vor für den ein Nummernbereich gilt. In Spalte E wird die Nummer eingetragen. Allerdings habe ich in Spalte H weitere Werte die einen eigenen Nummernbereich bedürfen. Kann man auch sagen "wenn in Spalte A der Wert "OrtA" steht und in Spalte H "Bedingung1" nutze Nummernbereich 1" aber wenn in Spalte A "Ort A" und in Spalte H "Bedingung2" steht, nutze Nummernbereich 2 und so weiter ? 

Viele Grüße 

Foley

[Elex]

Hi
 
In deinem neuen Thema habe ich gesehen dass du noch Erweiterungen an der Datei vornehmen möchtest.
 
Man kann durch Doppelklick sicher mehrere Dingen trennen und ausführen lassen. Aber dann sollte solches  Verhalten erst einmal geklärt sein.

Aber wie gesagt, für mich ist es nicht schlimm ob ich nun auf den Ort oder auf das Feld für die Nummer klicken muss. Macht ja beides irgendwie Sinn.

Denn es dürfte keine Reaktion geben wenn du den Doppelklick nicht in Spalte A ausführst. 

 
Zu deiner Vorstellung mit der Spalte H. Möglichkeiten gibt es meistens viele. Und wenn du mit all den neben Bedingungen wie „entfernt“ nicht den Überblick verlierst ist es ja ok. Also ich verstehe dich so das wenn in Spalte H nichts steht wird die Nummernvergabe durchgeführt wie bisher. Steht in H eine 1 dann eine Nummer aus Bereich 1 , eine 3 in H dann Nummer aus Bereich 3 usw.
 
Zum Testen:

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Range
Dim i As Long

If Target.Column = 1 Then
  With Sheets("Tabelle2").Range("A2", Sheets("Tabelle2").Range("A2").End(xlDown))
    Set n = .Find(Target.Value, LookIn:=xlValues)
     If Not n Is Nothing Then
      If Target.Cells.Offset(, 4) = "" Then
        If Target.Cells.Offset(, 7) <> "" Then
           i = Target.Cells.Offset(, 7).Value
           Target.Cells.Offset(, 4) = Sheets("Tabelle2").Cells(1 + i, 2)
        Else
            If MsgBox("Soll " & Target.Address & " " & Target.Value & _
              " die Nr. " & n.Offset(, 1) & " zugewiesen werden?", _
              vbYesNo Or vbQuestion, "Zuweisen") = vbYes Then
              Target.Cells.Offset(, 4) = n.Offset(, 1)
            End If
        End If
      End If
     Else
       MsgBox "Ort nicht gefunden."
     End If
  End With
  Cancel = True
End If
End Sub

In Tabelle 2 wirst du die Formel verwenden müssen. Vermerk "entfernt" steht hier in Spalte (F) evtl auch noch anpassen.
B2   =WENNFEHLER(VERGLEICH(1;1*(WENNFEHLER(VERGLEICH(ZEILE(INDEX(A:A;C2;):INDEX(A:A;D2;));WENN((Tabelle1!$F$2:$F$5000<>"Entfernt");Tabelle1!$E$2:$E$5000);0);0)=0);0)+D1;"alle Vergeben")   'strg, shift, Enter

[Foley]

Hi Elex

du hast natürlich Recht. Wenn ich weitere Funktionen einbauen möchte, sollte ich erst klären warum sich die Zelle für den Doppelklick plötzlich verschoben hat. Ich mache die Änderungen die den Blattschutz aufheben und wieder setzen rückgängig und sehe mal wie es dann ist und ob es auch mit Blattschutz, bei dem die Spalte für die Nummern gesperrt ist, funktioniert.

Ich würde gerne verschiedene Dinge an verschiedenen Orten Prüfen und diesen pro Ort und Ding einen eigenen Nummernbereich zuordnen.

Also 

SpalteA OrtA und Spalte H DingA = Nummernbereich 1
SpalteA OrtB und Spalte H DingA = Nummernbereich 2 

und so weiter .... aber:

SpalteA OrtA und Spalte H DingB = Nummernbereich 5 (Ich weiß noch nicht wieviele Nummernbereiche ich brauchen werde)

In Spalte A und H sollen also immer Bedingungen stehen. Nur ob "entfernt" auftaucht oder nicht soll variabel sein. 

Ich werde leider erst heute Nachmittag dazu kommen deinen Code zu testen, bedanke mich aber auf jeden Fall schonmal für deine Hilfe und melde mich dann wieder.

Viele Grüße

Foley