Kategorie szkoleń | Egzaminy | Kontakt
  • 1
  • 0
  • 1

Dzień Dobry.

Przygotowuję formularz dla użytkownika zewnętrznego. Chciałabym aby formularz blokował w danej kolumnie wprowadzenie danych w innym formacie niż numerycznym wraz z MsgBoxem o konieczności korekty, jeśli wprowadzony zostanie np tekst. 

Zwykła walidacja danych zadziała jedynie przy wprowadzaniu danych z ręki, nie zadziała jeśli dane są przeklejane do formularza ze schowka, więc sądzę, że VBA mógłby być tutaj rozwiązaniem. Jakiś pomysł?

Póki co udało mi się stworzyć pętlę sprawdzającą, czy kolumna zawiera wartości numeryczne.
Czy jest możliwość, żeby VBA się odpalało automatycznie np. przed zamknięciem pliku, tak aby wymusić na użytkowniku wprowadzenie poprawnych danych?

Sub TestColumn()
Dim komorka As Range
Dim Worksheet As Worksheet

ThisWorkbook.Sheets("report").Select
Range(Range("J2"), Range("J2").End(xlDown)).Select

For Each komorka In Selection

If isnumeric(komorka.Value) = False Then
MsgBox "Column XYZ should include numeric values"
End If

Next komorka

End Sub

 

Maria_Karczewska
  • Zapytał
  • @ Maria_Karczewska | 22.04.2020
    • 0
    • 0
    • 0

Odpowiedź (1)

  • 0

Do wykrywania zmian na bieżąco możesz użyć zdarzenia Worksheet_Change (kod dodaj w module arkusza, w którym chcesz osiągnąć efekt):

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns("J:J")) Is Nothing Then
    Set Rng = Range("J2").Resize(UsedRange.Rows.Count - 1, 1)
    If Application.Count(Rng) <> Application.CountA(Rng) Then MsgBox "Wprowadź liczbę!"
    Set Rng = Nothing
End If

End Sub

Jeśli chcesz to weryfikować przy zapisie, wklej poniższy kod w module Ten_skoroszyt:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Worksheets("Arkusz1")
   Set Rng = .Range("J2").Resize(.UsedRange.Rows.Count - 1, 1)
End With

If Application.Count(Rng) <> Application.CountA(Rng) Then
  MsgBox "W kolumnie J znajdują się dane inne niż liczbowe, popraw je przed zapisaniem pliku!", vbExclamation
  Cancel = True
End If

Set Rng = Nothing

End Sub

Kilka wskazówek do Twojego kodu:

1) Słowo Worksheet jest jedną z klas w VBA, lepiej nie nazywać tak zmiennej.

2) Unikaj używania metody Select, spowalnia ona jedynie wykonywanie kodu i niezmiernie rzadko jej użycie ma sens. Zamiast Range.Select i Selection.cośtam rób po prostu Range.cośtam.

3) Funkcja IsNumeric sprawdza, czy wartość jest liczbą lub może być przekonwertowana na liczbę - czyli przepuści np. wpis '1 (apostrof liczba), który oficjalnie jest tekstem, choć i nie powinien stwarzać problemów w formułach obliczeniowych. Nie wiem, czy to będzie miało znaczenie u Ciebie, ale może warto skorzystać z WorksheetFunction.IsNumber.

4) Metoda End(xlDown) potrafi być dość zawodna, szczególnie w kontakcie z użytkownikiem zewnętrznym :) Nie uwzględni np. luki w danych (zatrzyma się przed pierwszym pustym wierszem), a przy pustej kolumnie spowoduje wykonanie pętli na ponad milionie komórek. Wiele zależy od konkretnego przypadku, ale często lepiej użyć odwrotnego podejścia: Cells(Rows.Count, "J").End(xlUp) lub, jak w moim kodzie, odnieść się do aktualnie używanego zakresu (UsedRange, czyli od A1 do najdalszej uzupełnionej/sformatowanej komórki) i z niego odczytać maksymalny wiersz arkusza.

Mirosław_Janiak
  • Odpowiedział
  • @ Mirosław_Janiak | 24.04.2020
    • 1
    • 7
    • 1