Search VBA did not show results

M

Mantol

New Member
Joined
Oct 11, 2022
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
Hi,
I Am kind of new with VBA code. I recently created a form that will populate the data also with a search function. Anyway, I am using ".xlsb" format. Unfortunately, the search is not working where the data did not appear in the 2nd list box. Your guidance is very much appreciated. I have uploaded the files for your viewing and editing.



Anyway, my codes are as below.
-------------------------------------------------------------------------------------------


Private Sub addcmd_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'------------------------Validation---------------------------------
If Me.dpname.Value = "" Then
MsgBox "Please insert Department name", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.dptadd.Value = "" Then
MsgBox "Please insert department address or location", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.divcom.Value = "" Then
MsgBox "Please select department area", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ctname.Value = "" Then
MsgBox "Contact person for the department required", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ctno.Value = "" Then
MsgBox "Please insert the department contact no", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.nid.Value = "" Then
MsgBox "Please insert device Unique ID", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.serno.Value = "" Then
MsgBox "Please insert device serial number", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ipinfo.Value = "" Then
MsgBox "Please provide device IP information", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.snet.Value = "" Then
MsgBox "Please provide device network subnet", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.gway.Value = "" Then
MsgBox "Please provide device gateway IP", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.vlinfo.Value = "" Then
MsgBox "Please provide additional information of the device network", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.cirt.Value = "" Then
MsgBox "Please provide Circuit ID for the sites", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.netcatcom.Value = "" Then
MsgBox "Please select the device network category", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.netsubcom.Value = "" Then
MsgBox "Please select the device network sub-category", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ispcom.Value = "" Then
MsgBox "Please select the network ISP", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.snscom.Value = "" Then
MsgBox "Please select SAINS support team for the device", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.bdwh.Value = "" Then
MsgBox "Please provide link speed for the node", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.statcom.Value = "" Then
MsgBox "Please select status of the device", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.remf.Value = "" Then
MsgBox "Please provide additional information if any", vbInformation, "Centralize Database System"
Exit Sub
End If
'-------------------------------------------------------------------
sh.Range("A" & last_row + 1).Value = "=Row()-1"
sh.Range("B" & last_row + 1).Value = Me.dpname.Value
sh.Range("C" & last_row + 1).Value = Me.dptadd.Value
sh.Range("D" & last_row + 1).Value = Me.divcom.Value
sh.Range("E" & last_row + 1).Value = Me.ctname.Value
sh.Range("F" & last_row + 1).Value = Me.ctno.Value
sh.Range("G" & last_row + 1).Value = Me.nid.Value
sh.Range("H" & last_row + 1).Value = Me.serno.Value
sh.Range("I" & last_row + 1).Value = Me.ipinfo.Value
sh.Range("J" & last_row + 1).Value = Me.snet.Value
sh.Range("K" & last_row + 1).Value = Me.gway.Value
sh.Range("L" & last_row + 1).Value = Me.vlinfo.Value
sh.Range("M" & last_row + 1).Value = Me.cirt.Value
sh.Range("N" & last_row + 1).Value = Me.netcatcom.Value
sh.Range("O" & last_row + 1).Value = Me.netsubcom.Value
sh.Range("P" & last_row + 1).Value = Me.ispcom.Value
sh.Range("Q" & last_row + 1).Value = Me.snscom.Value
sh.Range("R" & last_row + 1).Value = Me.bdwh.Value
sh.Range("S" & last_row + 1).Value = Me.statcom.Value
sh.Range("T" & last_row + 1).Value = Me.remf.Value
sh.Range("U" & last_row + 1).Value = Now
'---------------------------------------------------------------------
Me.dptname.Value = ""
Me.dptadd.Value = ""
Me.divcom.Value = ""
Me.ctname.Value = ""
Me.ctno.Value = ""
Me.nid.Value = ""
Me.serno.Value = ""
Me.ipinfo.Value = ""
Me.snet.Value = ""
Me.gway.Value = ""
Me.vlinfo.Value = ""
Me.cirt.Value = ""
Me.netcatcom.Value = ""
Me.netsubcom.Value = ""
Me.ispcom.Value = ""
Me.snscom.Value = ""
Me.statcom.Value = ""
Me.remf.Value = ""
'---------------------------------------------------------------------
Call refresh_CDSDB

End Sub

Private Sub clrcmd_Click()

Me.dpname.Value = ""
Me.dptadd.Value = ""
Me.divcom.Value = ""
Me.ctname.Value = ""
Me.ctno.Value = ""
Me.nid.Value = ""
Me.serno.Value = ""
Me.ipinfo.Value = ""
Me.snet.Value = ""
Me.gway.Value = ""
Me.vlinfo.Value = ""
Me.cirt.Value = ""
Me.netcatcom.Value = ""
Me.netsubcom.Value = ""
Me.ispcom.Value = ""
Me.snscom.Value = ""
Me.bdwh.Value = ""
Me.statcom.Value = ""
Me.remf.Value = ""
Me.schcom.Value = ""
Me.schtxt.Value = ""
Me.index.Value = ""

End Sub

Private Sub datalist_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.index.Value = Me.datalist.List(Me.datalist.ListIndex, 0)
Me.dpname.Value = Me.datalist.List(Me.datalist.ListIndex, 1)
Me.dptadd.Value = Me.datalist.List(Me.datalist.ListIndex, 2)
Me.divcom.Value = Me.datalist.List(Me.datalist.ListIndex, 3)
Me.ctname.Value = Me.datalist.List(Me.datalist.ListIndex, 4)
Me.ctno.Value = Me.datalist.List(Me.datalist.ListIndex, 5)
Me.nid.Value = Me.datalist.List(Me.datalist.ListIndex, 6)
Me.serno.Value = Me.datalist.List(Me.datalist.ListIndex, 7)
Me.ipinfo.Value = Me.datalist.List(Me.datalist.ListIndex, 8)
Me.snet.Value = Me.datalist.List(Me.datalist.ListIndex, 9)
Me.gway.Value = Me.datalist.List(Me.datalist.ListIndex, 10)
Me.vlinfo.Value = Me.datalist.List(Me.datalist.ListIndex, 11)
Me.cirt.Value = Me.datalist.List(Me.datalist.ListIndex, 12)
Me.netcatcom.Value = Me.datalist.List(Me.datalist.ListIndex, 13)
Me.netsubcom.Value = Me.datalist.List(Me.datalist.ListIndex, 14)
Me.ispcom.Value = Me.datalist.List(Me.datalist.ListIndex, 15)
Me.snscom.Value = Me.datalist.List(Me.datalist.ListIndex, 16)
Me.bdwh.Value = Me.datalist.List(Me.datalist.ListIndex, 17)
Me.statcom.Value = Me.datalist.List(Me.datalist.ListIndex, 18)
Me.remf.Value = Me.datalist.List(Me.datalist.ListIndex, 19)

End Sub

Private Sub delcmd_Click()

If Me.index.Value = "" Then
MsgBox "Please select the data to DELETE"
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(CLng(Me.index.Value), sh.Range("A:A"), 0)
'-----------------------------------------------------
sh.Range("A" & selected_row).EntireRow.Delete
'-----------------------------------------------------
Me.dpname.Value = ""
Me.dptadd.Value = ""
Me.divcom.Value = ""
Me.ctname.Value = ""
Me.ctno.Value = ""
Me.nid.Value = ""
Me.serno.Value = ""
Me.ipinfo.Value = ""
Me.snet.Value = ""
Me.gway.Value = ""
Me.vlinfo.Value = ""
Me.netcatcom.Value = ""
Me.netsubcom.Value = ""
Me.ispcom.Value = ""
Me.snscom.Value = ""
Me.statcom.Value = ""
Me.remf.Value = ""
Me.index.Value = ""

Call refresh_CDSDB

End Sub

Private Sub excmd_Click()

MsgBox "Do you to exit", vbYesNo + vbInformation, "Centralized Database System v1.0"
Unload Me

End Sub

Private Sub savecmd_Click()

ThisWorkbook.Save
MsgBox "Data Saved", vbInformation, "Centralized Database System v1.0"

End Sub

Private Sub schcmd_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim selected_row As Long
On Error Resume Next

Worksheets("CDSDB").Sheets("A:U").Value = Me.schcom.Value

selected_row = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(Me.schtxt.Value, sh.Range("A2:U2"), 0), Application.WorksheetFunction.Match(Application.WorksheetFunction.ValueToText(Me.schtxt.Value), sh.Range("A2:U2"), 0))
Me.index.Value = Worksheets("CDSDB").Cells(selected_row, 1).Value
Me.dpname.Value = Worksheets("CDSDB").Cells(selected_row, 2).Value
Me.dptadd.Value = Worksheets("CDSDB").Cells(selected_row, 3).Value
Me.divcom.Value = Worksheets("CDSDB").Cells(selected_row, 4).Value
Me.ctname.Value = Worksheets("CDSDB").Cells(selected_row, 5).Value
Me.ctno.Value = Worksheets("CDSDB").Cells(selected_row, 6).Value
Me.nid.Value = Worksheets("CDSDB").Cells(selected_row, 7).Value
Me.serno.Value = Worksheets("CDSDB").Cells(selected_row, 8).Value
Me.ipinfo.Value = Worksheets("CDSDB").Cells(selected_row, 9).Value
Me.snet.Value = Worksheets("CDSDB").Cells(selected_row, 10).Value
Me.gway.Value = Worksheets("CDSDB").Cells(selected_row, 11).Value
Me.vlinfo.Value = Worksheets("CDSDB").Cells(selected_row, 12).Value
Me.cirt.Value = Worksheets("CDSDB").Cells(selected_row, 13).Value
Me.netcatcom.Value = Worksheets("CDSDB").Cells(selected_row, 14).Value
Me.netsubcom.Value = Worksheets("CDSDB").Cells(selected_row, 15).Value
Me.ispcom.Value = Worksheets("CDSDB").Cells(selected_row, 16).Value
Me.snscom.Value = Worksheets("CDSDB").Cells(selected_row, 17).Value
Me.bdwh.Value = Worksheets("CDSDB").Cells(selected_row, 18).Value
Me.statcom.Value = Worksheets("CDSDB").Cells(selected_row, 19).Value
Me.remf.Value = Worksheets("CDSDB").Cells(selected_row, 20).Value

With Me.schlist
.ColumnHeads = True
.ColumnCount = 21
.ColumnWidths = "30,150,150,80,100,100,150,100,100,100,100,150,150,150,120,150,100,150,150,150,100"

If selected_row = 1 Then
.RowSource = "CDSDB!A2:U2"
Else
.RowSource = "CDSDB!A2:U" & last_row
End If

End With

Call refresh_CDSDB


End Sub

Private Sub schlist_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.index.Value = Me.datalist.List(Me.datalist.ListIndex, 0)
Me.dpname.Value = Me.datalist.List(Me.datalist.ListIndex, 1)
Me.dptadd.Value = Me.datalist.List(Me.datalist.ListIndex, 2)
Me.divcom.Value = Me.datalist.List(Me.datalist.ListIndex, 3)
Me.ctname.Value = Me.datalist.List(Me.datalist.ListIndex, 4)
Me.ctno.Value = Me.datalist.List(Me.datalist.ListIndex, 5)
Me.nid.Value = Me.datalist.List(Me.datalist.ListIndex, 6)
Me.serno.Value = Me.datalist.List(Me.datalist.ListIndex, 7)
Me.ipinfo.Value = Me.datalist.List(Me.datalist.ListIndex, 8)
Me.snet.Value = Me.datalist.List(Me.datalist.ListIndex, 9)
Me.gway.Value = Me.datalist.List(Me.datalist.ListIndex, 10)
Me.vlinfo.Value = Me.datalist.List(Me.datalist.ListIndex, 11)
Me.cirt.Value = Me.datalist.List(Me.datalist.ListIndex, 12)
Me.netcatcom.Value = Me.datalist.List(Me.datalist.ListIndex, 13)
Me.netsubcom.Value = Me.datalist.List(Me.datalist.ListIndex, 14)
Me.ispcom.Value = Me.datalist.List(Me.datalist.ListIndex, 15)
Me.snscom.Value = Me.datalist.List(Me.datalist.ListIndex, 16)
Me.bdwh.Value = Me.datalist.List(Me.datalist.ListIndex, 17)
Me.statcom.Value = Me.datalist.List(Me.datalist.ListIndex, 18)
Me.remf.Value = Me.datalist.List(Me.datalist.ListIndex, 19)

End Sub

Private Sub schtxt_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim selected_row As Long
On Error Resume Next

Worksheets("CDSDB").Sheets("A:U").Value = Me.schcom.Value

selected_row = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(Me.schcom.Value, sh.Range("A2:U2"), 0), Application.WorksheetFunction.Match(Application.WorksheetFunction.ValueToText(Me.schcom.Value), sh.Range("A2:U2"), 0))
Me.index.Value = Worksheets("CDSDB").Cells(selected_row, 1).Value
Me.dpname.Value = Worksheets("CDSDB").Cells(selected_row, 2).Value
Me.dptadd.Value = Worksheets("CDSDB").Cells(selected_row, 3).Value
Me.divcom.Value = Worksheets("CDSDB").Cells(selected_row, 4).Value
Me.ctname.Value = Worksheets("CDSDB").Cells(selected_row, 5).Value
Me.ctno.Value = Worksheets("CDSDB").Cells(selected_row, 6).Value
Me.nid.Value = Worksheets("CDSDB").Cells(selected_row, 7).Value
Me.serno.Value = Worksheets("CDSDB").Cells(selected_row, 8).Value
Me.ipinfo.Value = Worksheets("CDSDB").Cells(selected_row, 9).Value
Me.snet.Value = Worksheets("CDSDB").Cells(selected_row, 10).Value
Me.gway.Value = Worksheets("CDSDB").Cells(selected_row, 11).Value
Me.vlinfo.Value = Worksheets("CDSDB").Cells(selected_row, 12).Value
Me.cirt.Value = Worksheets("CDSDB").Cells(selected_row, 13).Value
Me.netcatcom.Value = Worksheets("CDSDB").Cells(selected_row, 14).Value
Me.netsubcom.Value = Worksheets("CDSDB").Cells(selected_row, 15).Value
Me.ispcom.Value = Worksheets("CDSDB").Cells(selected_row, 16).Value
Me.snscom.Value = Worksheets("CDSDB").Cells(selected_row, 17).Value
Me.bdwh.Value = Worksheets("CDSDB").Cells(selected_row, 18).Value
Me.statcom.Value = Worksheets("CDSDB").Cells(selected_row, 19).Value
Me.remf.Value = Worksheets("CDSDB").Cells(selected_row, 20).Value

End Sub

Private Sub updcmd_Click()
If Me.index.Value = "" Then
MsgBox "Please select the data to update"
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(CLng(Me.index.Value), sh.Range("A:A"), 0)

'------------------------Validation---------------------------------
If Me.dpname.Value = "" Then
MsgBox "Please insert Department name", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.dptadd.Value = "" Then
MsgBox "Please insert department address or location", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.divcom.Value = "" Then
MsgBox "Please select department area", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ctname.Value = "" Then
MsgBox "Contact person for the department required", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ctno.Value = "" Then
MsgBox "Please insert the department contact no", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.nid.Value = "" Then
MsgBox "Please insert device Unique ID", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.serno.Value = "" Then
MsgBox "Please insert device serial number", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ipinfo.Value = "" Then
MsgBox "Please provide device IP information", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.snet.Value = "" Then
MsgBox "Please provide device network subnet", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.gway.Value = "" Then
MsgBox "Please provide device gateway IP", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.vlinfo.Value = "" Then
MsgBox "Please provide additional information of the device network", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.cirt.Value = "" Then
MsgBox "Please provide Circuit ID for the sites", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.netcatcom.Value = "" Then
MsgBox "Please select the device network category", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.netsubcom.Value = "" Then
MsgBox "Please select the device network sub-category", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.ispcom.Value = "" Then
MsgBox "Please select the network ISP", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.snscom.Value = "" Then
MsgBox "Please select SAINS support team for the device", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.bdwh.Value = "" Then
MsgBox "Please provide link speed for the node", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.statcom.Value = "" Then
MsgBox "Please select status of the device", vbCritical + vbInformation, "Centralize Database System"
Exit Sub
End If
If Me.remf.Value = "" Then
MsgBox "Please provide additional information if any", vbInformation, "Centralize Database System"
Exit Sub
End If
'-------------------------------------------------------------------

sh.Range("B" & selected_row + 1).Value = Me.dpname.Value
sh.Range("C" & selected_row + 1).Value = Me.dptadd.Value
sh.Range("D" & selected_row + 1).Value = Me.divcom.Value
sh.Range("E" & selected_row + 1).Value = Me.ctname.Value
sh.Range("F" & selected_row + 1).Value = Me.ctno.Value
sh.Range("G" & selected_row + 1).Value = Me.nid.Value
sh.Range("H" & selected_row + 1).Value = Me.serno.Value
sh.Range("I" & selected_row + 1).Value = Me.ipinfo.Value
sh.Range("J" & selected_row + 1).Value = Me.snet.Value
sh.Range("K" & selected_row + 1).Value = Me.gway.Value
sh.Range("L" & selected_row + 1).Value = Me.vlinfo.Value
sh.Range("M" & selected_row + 1).Value = Me.cirt.Value
sh.Range("N" & selected_row + 1).Value = Me.netcatcom.Value
sh.Range("O" & selected_row + 1).Value = Me.netsubcom.Value
sh.Range("P" & selected_row + 1).Value = Me.ispcom.Value
sh.Range("Q" & selected_row + 1).Value = Me.snscom.Value
sh.Range("R" & selected_row + 1).Value = Me.bdwh.Value
sh.Range("S" & selected_row + 1).Value = Me.statcom.Value
sh.Range("T" & selected_row + 1).Value = Me.remf.Value
sh.Range("U" & selected_row + 1).Value = Now
'---------------------------------------------------------------------
Me.dptname.Value = ""
Me.dptadd.Value = ""
Me.divcom.Value = ""
Me.ctname.Value = ""
Me.ctno.Value = ""
Me.nid.Value = ""
Me.serno.Value = ""
Me.ipinfo.Value = ""
Me.snet.Value = ""
Me.gway.Value = ""
Me.vlinfo.Value = ""
Me.cirt.Value = ""
Me.netcatcom.Value = ""
Me.netsubcom.Value = ""
Me.ispcom.Value = ""
Me.snscom.Value = ""
Me.statcom.Value = ""
Me.remf.Value = ""
Me.index.Value = ""
Me.schlist.Value = ""

'---------------------------------------------------------------------
Call refresh_CDSDB

End Sub

Private Sub UserForm_Activate()

With Me.divcom
.Clear
.AddItem "Kuching"
.AddItem "Kota Samarahan"
.AddItem "Serian"
.AddItem "Sri Aman"
.AddItem "Betong"
.AddItem "Sarikei"
.AddItem "Sibu"
.AddItem "Mukah"
.AddItem "Kapit"
.AddItem "Bintulu"
.AddItem "Miri"
.AddItem "Limbang"
End With

With Me.netcatcom
.Clear
.AddItem "SarawakNet"
.AddItem "Sarawak Service-KIOSK"
.AddItem "VSAT"
.AddItem "CITN"
.AddItem "ELV"
.AddItem "DSE"
End With

With Me.netsubcom
.Clear
.AddItem "SarawakNet-WAN"
.AddItem "SarawakNet-LAN"
.AddItem "Sarawak Service-KIOSK"
.AddItem "CORS"
.AddItem "Application"
.AddItem "Firewall"
.AddItem "Servers"
.AddItem "ELV-UPS"
.AddItem "ELV-CCTV"
.AddItem "CITN"
.AddItem "VSAT"
.AddItem "ForestNet"
.AddItem "DSE-Wifi Kampung"
End With

With Me.ispcom
.Clear
.AddItem "SACOFA"
.AddItem "R10-VSAT"
.AddItem "R10-Dark Fiber"
.AddItem "TM"
.AddItem "SAINS"
End With

With Me.snscom
.Clear
.AddItem "DIS-Wireless"
.AddItem "DIS-Network Implementor"
.AddItem "NSS-LAN"
.AddItem "TS-Hardware"
.AddItem "CSM-Security"
.AddItem "SSS-Application"
.AddItem "ISS-ELV"
.AddItem "DC-Servers"
End With

With Me.statcom
.Clear
.AddItem "Active"
.AddItem "Temporary Deactivated"
.AddItem "Deactivated"
.AddItem "Node Upgraded"
.AddItem "Node Migrated"
.AddItem "Node Relocated"
End With

With Me.schcom
.Clear
.AddItem "Department Name"
.AddItem "IPv4 / IPv6"
.AddItem "Network ID"
.AddItem "Status"
End With



Call refresh_CDSDB

End Sub

Sub refresh_CDSDB()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.datalist
.ColumnHeads = True
.ColumnCount = 21
.ColumnWidths = "30,150,150,80,100,100,150,100,100,100,100,150,150,150,120,150,100,150,150,150,100"

If last_row = 1 Then
.RowSource = "CDSDB!A2:U2"
Else
.RowSource = "CDSDB!A2:U" & last_row
End If

End With

End Sub

Sub search_CDSDB()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CDSDB")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(Me.schtxt.Value, sh.Range("A2:U2"), 0), Application.WorksheetFunction.Match(Application.WorksheetFunction.ValueToText(Me.schtxt.Value), sh.Range("A2:U2"), 0))

With Me.datalist
.ColumnHeads = True
.ColumnCount = 21
.ColumnWidths = "30,150,150,80,100,100,150,100,100,100,100,150,150,150,120,150,100,150,150,150,100"

If selected_row = 1 Then
.RowSource = "CDSDB!A2:U2"
Else
.RowSource = "CDSDB!A2:U" & last_row
End If

End With

End Sub
 
Last edited by a moderator:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You must log in or register to reply here.

Similar threads

P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
592
pure vito
P
G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
178
genesix
G
X
  • Solved
When update the record the entries are saved in the columnheaders ^^
Replies
2
Views
411
xainthowell
X
X
  • Solved
Displayed numeric date instead of the entered format date
Replies
6
Views
482
hicksi
hicksi
X
  • Solved
It didn't change the forecolor of the textbox
Replies
4
Views
520
Fluff
Fluff

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top