I was wondering if anyone can please help me with this task I would to create a userform that will search a workbook in excel with mulitiple sheet, it could be 5-endless worksheets The way i would like to do this is a new tab is created when a new client is add to our database and be able to seach curtain critiera such Hospital name, Contact name,services,phone numbers(if possible)and User name (if Possible) It sounds more complacted then it seems i am going to try to attach the file and code is there anyone whom can help i will be thankful - [Option Explicit]
-
-
'Module Level Variables
-
Dim rRange As Range
-
Dim strFind1 As String
-
Dim strFind2 As String
-
Dim strFind3 As String
-
-
-
Private Sub ComboBox1_Change()
-
'Pass chosen value to String variable strFind1
-
strFind1 = ComboBox1
-
'Enable ComboBox2 only if value is chosen
-
ComboBox2.Enabled = Not strFind1 = vbNullString
-
End Sub
-
Private Sub ComboBox2_Change()
-
'Pass chosen value to String variable strFind1
-
strFind2 = ComboBox2
-
'Enable ComboBox3 only if value is chosen
-
End Sub
-
-
-
Private Sub CommandButton1_Click()
-
'Procedure level variables
-
Dim lCount As Long
-
Dim lOccur As Long
-
Dim rCell As Range
-
Dim rCell2 As Range
-
Dim rCell3 As Range
-
Dim bFound As Boolean
-
-
'At least one value, from ComboBox1 must be chosen
-
If strFind1 & strFind2 & strFind3 = vbNullString Then
-
MsgBox "No items to find chosen", vbCritical
-
Exit Sub 'Go no further
-
ElseIf strFind1 = vbNullString Then
-
MsgBox "A value from " & Label1.Caption _
-
& " must be chosen", vbCritical
-
Exit Sub 'Go no further
-
End If
-
-
'Clear any old entries
-
On Error Resume Next
-
ListBox1.Clear
-
On Error GoTo 0
-
-
'If String variable are empty pass the wildcard character
-
If strFind2 = vbNullString Then strFind2 = "*"
-
If strFind3 = vbNullString Then strFind3 = "*"
-
-
'Set range variable to first cell in table.
-
Set rCell = rRange.Cells(1, 1)
-
'Pass the number of times strFind1 occurs
-
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)
-
-
'Loop only as many times as strFind1 occurs
-
For lCount = 1 To lOccur
-
'Set the range variable to the found cell. This is then also _
-
used to start the next Find from (After:=rCell)
-
Set rCell = rRange.Columns(1).Find(What:=strFind1, After:=rCell, _
-
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
-
SearchDirection:=xlNext, MatchCase:=False)
-
'Check each find to see if strFind2 and strFind3 occur _
-
on the same row.
-
If rCell(1, 2) Like strFind2 And rCell(1, 3) Like strFind3 Then
-
bFound = True 'Used to not show message box for no value found.
-
'Add the address of the found cell and the cell on the _
-
same row but 2 columns to the right.
-
ListBox1.AddItem rCell.Address & ":" & rCell(1, 3).Address
-
End If
-
Next lCount
-
-
If bFound = False Then 'No match
-
MsgBox "Sorry, no matches", vbOKOnly
-
End If
-
End Sub
-
-
Private Sub CommandButton2_Click()
-
'Close UserForm
-
Unload Me
-
End Sub
-
-
-
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
-
'Check for range addresses
-
If ListBox1.ListCount = 0 Then Exit Sub
-
'GoTo doubled clicked address
-
Application.Goto Range(ListBox1.Text), True
-
End Sub
-
-
Private Sub UserForm_Initialize()
-
'Procedure level module
-
Dim lRows As Long
-
-
'Set Module level range variable to CurrentRegion _
-
of the Selection
-
Set rRange = Selection.CurrentRegion
-
If rRange.Rows.Count < 2 Then ' Only 1 row
-
MsgBox "Please select any cell in your table first", vbCritical
-
Unload Me 'Close Userform
-
Exit Sub
-
Else
-
-
With rRange
-
'Set Label Captions to the Table headings
-
Label1.Caption = .Cells(1, 1)
-
Label2.Caption = .Cells(1, 2)
-
Label3.Caption = .Cells(1, 3)
-
-
'Set RowSource of ComboBoxes to the appropriate columns _
-
inside the table
-
strSheet = ListBox1.List(ListBox1.ListIndex, 1)
-
strAddress = ListBox1.List(ListBox1.ListIndex, 2)
-
-
-
End With
-
End If
-
End Sub
-
-
Private Sub UserForm_Terminate()
-
'Destroy Module level variables
-
Set rRange = Nothing
-
strFind1 = vbNullString
-
strFind2 = vbNullString
-
End Sub
update:
By the way the Log-n password is PLS4-Username Password PLS
5 1688
Is this Office 2003?
In the ZIP is an .LNK file and not a .XLS or a .XLSM!
I am very new to this here is a new copy of what i need help with
This is still a LINK to a database we don't have.
Is it possible to put some data in an Excel file and attach this file in Bytes ? (also with you form)
Because in your code there is no connection to a database, is your data already in the Excel workbook ?
Or am I wrong ?
This is the very beginging stages of what i am trying to accomplish I did not realize until now that if I make a change in my workbook it will also change what i posted on Bytes can i please have an email for you in order for me to forward this file to you, i cant seem to upload this correctly.
Or if you can tell me another way of getting this to you i really need help!!! and would be thankful for any guidedance in this matter.
Is it possible to send us an Excel file with some data (+- 100 rows of data in some sheets) (the file can't be to big !!) in a ZIP file ?
I have attached a DOC and a PDF explaining how to do this.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Continental Translations |
last post by:
Not too sure if this is the correct NG, but here goes....
I am in the process of creating a website
(http://www.continental-translations.com - well someone is creating it for
me) and I am hoping...
|
by: szymon_k |
last post by:
Hi.
How to set the userform as subform of some access form?
What I need to do is to insert some userform into the form, so that it
will be under the same conditions of resize, move and other...
|
by: gw.boswell |
last post by:
I have created a userform with textboxes for data entry. I also have a
command button that calls a second useform. A user enters certain
information in the first useform but if he/she wants to do...
|
by: williamyeow |
last post by:
Hi i am new to excel VB. can anyone tell me how to link userform to another userform?
My concept for my program
I have set up a userform with a couple of option. after the user has chose the...
|
by: brenty66 |
last post by:
I have a userform that is used as an interface for information stored on excel spreadsheets. The user enters bits of information into fields on the userform which are place on the corresponding...
| |
by: Guillermo |
last post by:
Hi!
Is it possible to load the full-text search module for the SQLite
version bundled with Python 2.5?
I've tested it and the stand-alone SQLite distribution doesn't seem to
include it...
|
by: pod |
last post by:
Hello
What I want to do is:
from this workbook (thisWB), I want to access a userform's properties that are in another Workbook (toolWB)
I can do this for worksheets, but I want to do it as...
|
by: esperanto234 |
last post by:
Hi, This is the Ultimate Question!!!
I already finished my program in VBA, it has a userform for filling data into Word.
It has a bunch of textboxes.
But people has to fill de information...
|
by: David Wilkinson |
last post by:
Hi,
I am reasonably new to Excel VBA. I have written a Userform (Wizard) with a large amount of code and I am hitting the limits of the number of lines of code or the famous undocumented limit of...
|
by: katie co |
last post by:
Basically I want the user to open a previously saved file. I wish for my program to create a userform that asks "what file would you like to open" and I would like the user to only have to type in a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |