Hi,
I have quit a lot of code in this workbook (doing other stuff than this function). Therefore I will post parts related to this only.
In the meantime I had changed mane of function to SprawdzNum as you suggested. (althught this wasn't cause of problem).
Workbook part: (I will move it later to module). If I do that would I need to change ThisWorkbook.functionname to Module1.functionname when I call this function?
Public Function SprawdzNum( g As String, num As Integer) As Integer
' -1 = nieprawidłowy numer grupy
' 0 = nie ma w bazie
' 1 - jest w bazie
Dim grupa As Variant
Dim k As Boolean
k = False
g = UCase(g)
For Each grupa In ThisWorkbook.grupy
If g = grupa Then
k = True
End If
Next grupa
If k = False Then
SprawdzNum = -1
Set ThisWorkbook.CurrentEdit = Nothing
Exit Function
End If
' poszukiwanie numeru
Dim aCell As Range
Dim lastrow As Long
lastrow = Worksheets(g).Range("A" & Rows.Count).End(xlUp).Row
Set aCell = Worksheets(g).Range("A1:A" & lastrow).Find(What:=num, After:=Worksheets(g).Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
SprawdzNum = 1
Set ThisWorkbook.CurrentEdit = Worksheets(g).Range("A" & aCell.Row, "EA" & aCell.Row)
Else
SprawdzNum = 0
Set ThisWorkbook.CurrentEdit = Nothing
End If
End Function
Userform part:
Dim formdate As String
Dim zdjecie_fp As String
Dim numer As Integer
Dim ilosc As Integer
Dim masa As Double
Dim line As String
Sub SprawdzNumer()
If Not numer = 0 Then
If Dodanie_nowego.Combo_grupa.ListIndex > -1 Then
Dim i As Integer
i = ThisWorkbook.SprawdzNum(Dodanie_nowego.Combo_grupa.Text, numer)
If i = 1 Then
MsgBox ("Numer już występuje. Proszę podać inny lub wybrać opcję 'AUTO'")
Dodanie_nowego.Text_numer.Value = vbNullString
numer = 0
ElseIf i = -1 Then
MsgBox ("Nieprawidłowy symbol grupy")
End If
End If
End If
' ElseIf Len(Dodanie_nowego.Text_numer.Text) < 1 Then
End Sub
Private Sub UserForm_Initialize()
Dim element As Variant
Dim gropa As Variant
Data.Caption = Format(Now(), "yyyy-mm-dd hh:mm")
formdate = Format(Now(), "yyyy-mm-dd hh:mm")
numer = 0
zdjecie_fp = vbNullString
For Each element In ThisWorkbook.osoby
Dodanie_nowego.Combo_osoby.AddItem (element)
'MsgBox (element)
Next element
Dodanie_nowego.Combo_osoby.ListIndex = 0
For Each grupa In ThisWorkbook.grupy
Combo_grupa.AddItem (grupa)
Next grupa
ThisWorkbook.edit = True
End Sub
Private Sub Text_numer_AfterUpdate()
If Dodanie_nowego.Combo_grupa.ListIndex = -1 Then
MsgBox ("Proszę wybrać grupę")
Exit Sub
End If
' jezeli wartość numeryczna
If Len(Text_numer.Value) > 0 And IsNumeric(Text_numer.Value) Then
numer = Int(Round(Text_numer.Value, 2) * 100)
Text_numer.Value = Format(Round(Text_numer.Value, 2) * 100, "0000-00")
' jeżeli jakieś dziwny wpis
ElseIf Len(Text_numer.Value) > 0 And Not Text_numer.Text Like "####-##" Then
MsgBox ("Prosze wprowadzic wartość numeryczną")
Text_numer.Value = vbNullString
numer = 0
Exit Sub
' jeżeli wartość w poprawnym formacie
ElseIf Len(Text_numer.Value) > 0 And Text_numer.Text Like "####-##" Then
numer = Int(Val(Left(Text_numer.Text, 4) & Right(Text_numer.Text, 2)))
Text_numer.Value = Format(numer, "0000-00")
' jeżeli puste
ElseIf Len(Text_numer.Value) < 1 Then
MsgBox ("Proszę wprowadzić numer lub zaznaczyć opcję 'AUTO'")
Text_numer.Value = vbNullString
numer = 0
Exit Sub
End If
Call SprawdzNumer
End Sub
Cris.
Bookmarks