Userform with search box (1 Viewer)

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
hello, i have a problem with the "Search" box in userform excel VBA (cmdsearch and cmdupdate)
can anyone help me please !
 

Attachments

  • 2019-12-11 (1).jpg
    2019-12-11 (1).jpg
    99.6 KB · Views: 185
  • 2019-12-11.jpg
    2019-12-11.jpg
    101.4 KB · Views: 187

vba_php

Forum Troll
Local time
Today, 06:08
Joined
Oct 6, 2019
Messages
2,884
the images don't show any comments or errors. what's the problem? i'm not an excel expert, but i might be able to give u something.
 

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
I can't upload Excel file .....
 

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
Less than 2 Mb but it a enable VBA file ! I tried many time but it doesn't work
 

vba_php

Forum Troll
Local time
Today, 06:08
Joined
Oct 6, 2019
Messages
2,884
Less than 2 Mb but it a enable VBA file ! I tried many time but it doesn't work
vba shouldn't matter. Gasman, does Jon have any executable blockers on this site? I can't imagine he does. some of the other forums on the web have serious high tech code that blocks a lot of code that is even embedded in < script > tags! https://www.dreamincode.net/forums/index.php is one of those places. it's annoying as hell. they use CLOUDFARE.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:08
Joined
Sep 21, 2011
Messages
14,038
If you have the xlsm extension, they are not catered for. Save as 97-2003 xls and try that.
 

Attachments

  • vbaUploadTest.xls
    27 KB · Views: 173

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
ok ! i can upload ! help me please :confused:
 

Attachments

  • hospital 2003.zip
    554.5 KB · Views: 177

isladogs

MVP / VIP
Local time
Today, 11:08
Joined
Jan 14, 2017
Messages
18,186
For info, you could just have zipped the original xlsm file
 

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
Code:
Private Sub cmdCancel_Click()
        Unload Me
End Sub
Private Sub cmdClear_Click()
'clear the form
For Each ctl In Me.Controls
 If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
 ctl.Value = ""
 ElseIf TypeName(ctl) = "CheckBox" Then
 ctl.Value = False
 End If
Next ctl
End Sub

Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
' check user iput
If Me.cboward.Value = "" Then
MsgBox "Please enter a ward.", vbExclamation, "Staff Expenses"
Me.cboward.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtHN.Value) Then
MsgBox "The HN box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtHN.SetFocus
Exit Sub
End If
If Me.txtname.Value = "" Then
MsgBox "Please enter a patients Names.", vbExclamation, "Staff Expenses"
Me.txtname.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtage.Value) Then
MsgBox "The Age box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtage.SetFocus
Exit Sub
End If
If Me.txtvillage.Value = "" Then
MsgBox "Please enter the Village.", vbExclamation, "Staff Expenses"
Me.txtvillage.SetFocus
Exit Sub
End If
If Me.cbodistrict.Value = "" Then
MsgBox "Please enter the District.", vbExclamation, "Staff Expenses"
Me.cbodistrict.SetFocus
Exit Sub
End If
If Me.cboprovince.Value = "" Then
MsgBox "Please enter the Province.", vbExclamation, "Staff Expenses"
Me.cboprovince.SetFocus
Exit Sub
End If
If Me.cbokind.Value = "" Then
MsgBox "Please enter the kind.", vbExclamation, "Staff Expenses"
Me.cbokind.SetFocus
Exit Sub
End If

If Me.txtcause.Value = "" Then
MsgBox "Please enter the Chief complaints.", vbExclamation, "Staff Expenses"
Me.txtcause.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtdatein.Value) Then
    MsgBox "The Date box must contain a date in.", vbExclamation, "Staff Expenses"
    Me.txtdatein.SetFocus
    Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("Patients").Range("A5").CurrentRegion.Rows.Count
With Worksheets("Patients").Range("A5")
.Offset(RowCount, 0).Value = Me.cboward.Value
.Offset(RowCount, 1).Value = Me.txtHN.Value
.Offset(RowCount, 2).Value = Me.txtname.Value
.Offset(RowCount, 3).Value = Me.txtage.Value
If Me.optionman.Value = True Then
    .Offset(RowCount, 4).Value = "Male"
    Else: .Offset(RowCount, 4).Value = "Female"
End If
.Offset(RowCount, 5).Value = Me.txtvillage.Value
.Offset(RowCount, 6).Value = Me.cbodistrict.Value
.Offset(RowCount, 7).Value = Me.cboprovince.Value
.Offset(RowCount, 8).Value = Me.cbokind.Value
.Offset(RowCount, 9).Value = Me.txtcause.Value
.Offset(RowCount, 10).Value = DateValue(Me.txtdatein.Value)
.Offset(RowCount, 11).Value = Me.cbodiagnosis.Value
.Offset(RowCount, 12).Value = DateValue(Me.txtdateout.Value)
If Me.chkdead.Value = True Then
.Offset(RowCount, 13).Value = "Yes"
Else: .Offset(RowCount, 13).Value = "No"
End If
.Offset(RowCount, 14).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
'Clear the form
For Each ctl In Me.Controls
 If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
 ctl.Value = ""
 ElseIf TypeName(ctl) = "CheckBox" Then
 ctl.Value = False
 End If
Next ctl
End Sub

Private Sub cmdsearch_Click()

End Sub


Private Sub cmdupdate_Click()

End Sub




cmdsearch_click() i want to search (RowCount, 1).Value = Me.txtHN.Value
(ID patients) and when i edit information already i can do update by click on CMDupdate_click


anyone can help ???
 
Last edited by a moderator:

isladogs

MVP / VIP
Local time
Today, 11:08
Joined
Jan 14, 2017
Messages
18,186
I've added code tags to make your code readable. That will help others help you!
In future please click the # button on the toolbar to do this yourself. Thanks
 

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
Upload new try , please every body help :eek::(:)
 

Attachments

  • try.zip
    553.7 KB · Views: 207

vba_php

Forum Troll
Local time
Today, 06:08
Joined
Oct 6, 2019
Messages
2,884
kankay,

I'm going to be nice about this. there's 2 things I'll say here:

1) I don't know where in the world you're at, but this is an English speaking forum, based in England, UK. You can't expect someone to help you by uploading a file with foreign text in it. I can't even interpret it. what is it? it looks like arabic.

2) You might not be intending this, but please don't beg for help. We do all we can here, but sounding desperate will probably yield you less results in terms of getting an answer.

I have no idea how to translate that file of yours and I'm not willing to learn. However, see the attached file. I'm sure this is close to what you're looking for. You can modify it by just copying my code, as you're already skilled in using .offset() and basic if/else functions. You should be able to use this example to get started. I wrote it based on what you have said so far. it is searching the records based on txtHN, which is what you said you wanted. it doesn't make sense though, because txtHN is NOT the name of you search textbox. the updates are only applied to columns that correspond to text box controls on your form. but again, you can modify what I did just by learning from copying it.

another thing you should know is that I had no idea how to solve this issue. but a 2 second google search and a 5 minute read of a microsoft article showed me how to code it. you could have done that yourself. I'm still somewhat young, so I'm still a little forgiving of people who don't put forth an effort to find an answer on the internet before asking a question like this, but I'm somewhat sure you didn't get an answer before I posted this one because a lot of the older guys here don't have the patience anymore. that's free advice. do with it what you will. I hope it helps.
 

Attachments

  • try - return example.zip
    596.9 KB · Views: 203
Last edited:

kanxay

Registered User.
Local time
Today, 04:08
Joined
May 18, 2019
Messages
37
Dear Mr. vba_php

when i press search icon (With empty ) the massage box will alert "ID entered was not found"
But when i fill wrong ID such as: 236598711621002346
then it Debug ???
 

Attachments

  • 2019-12-12.jpg
    2019-12-12.jpg
    94.8 KB · Views: 178

vba_php

Forum Troll
Local time
Today, 06:08
Joined
Oct 6, 2019
Messages
2,884
Dear Mr. vba_php

when i press search icon (With empty ) the massage box will alert "ID entered was not found"
But when i fill wrong ID such as: 236598711621002346
then it Debug ???
like i said, I'm no expert. .FIND method returns an OBJECT variable instead of VARIANT, which is surprising to me, but regardless, i just test this code behind the search button and it works fine. try this instead of what I wrote initially:
Code:
Private Sub cmdsearch_Click()

On Error GoTo err_handle

    With Sheet1
        Set found = .Cells.Find(what:=Me.txtHN, after:=Cells(6, 3), LookIn:=xlValues)
            If found Then
                rowFound = found.row
                Me.txtname = .Range("b" & CStr(found.row)).Offset(0, 1)
                Me.txtage = .Range("b" & CStr(found.row)).Offset(0, 2)
                Me.txtvillage = .Range("b" & CStr(found.row)).Offset(0, 4)
                Me.txtcause = .Range("b" & CStr(found.row)).Offset(0, 8)
                Me.txtdatein = .Range("b" & CStr(found.row)).Offset(0, 9)
            Else
found_is_empty:
                rowFound = 0
                MsgBox "ID entered was not found", vbCritical
                Exit Sub
            End If
    End With

err_handle:
    If Err.Number = 91 Then
        GoTo found_is_empty
    Else
        MsgBox Err.Description
    End If
End Sub
 

Users who are viewing this thread

Top Bottom