• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

User form lookup|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / User form lookup|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosUser form lookup
sp_PrintTopic sp_TopicIcon
User form lookup
Avatar
Rebecca Lebwohl

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 14, 2020
sp_UserOfflineSmall Offline
1
August 14, 2020 - 5:34 am
sp_Permalink sp_Print

I tried to use the code you gave in https://www.myonlinetraininghu.....-user-form but it is not working on my form when I adapt it for my workbook

Dim RecordRow As Long
Dim RecordRange As Range

' Turn off default error handling so Excel does not display
' an error if the record number is not found
On Error Resume Next

' Find the row in the table that the record is in
RecordRow = Application.Match(CLng(Me.txtPatientName.Value), Range("Requests[Patient Name]"), 0)

' Set RecordRange to the first cell in the found record
Set RecordRange = Range("Requests").Cells(1, 2).Offset(RecordRow - 1, 0)

' If an error has occured i.e the record number was not found
If Err.Number <> 0 Then

Me.lblErrorNoPatient.Visible = True
On Error GoTo 0
Exit Sub

End If

' Turn default error handling back on (Let Excel handle errors from now on)
On Error GoTo 0

' If the code gets to here the record number was found
' Hide the error message 'Not Found'
Me.lblErrorNoPatient.Visible = False
' and populate the form fields with the record's data
Me.txtPatientDOB.Value = RecordRange(1, 2).Offset(0, 1).Value
Me.txtPatientPhone.Value = RecordRange(1, 2).Offset(0, 2).Value
Me.cboPrimarySurgeon.Value = RecordRange(1, 2).Offset(0, 4).Value
Me.cboSecondarySurgeon.Value = RecordRange(1, 2).Offset(0, 5).Value

....

 

I had been using this code, but yours looked cleaner and I wanted to use your tutorials to build the insert/update macros. My existing code has issues, for instance if I have 2 rows with the same patient name, the update macro updates both rows.

If IsNull(Me.txtPatientDOB.Value) Then
pt_DOB = ""
Else
pt_DOB = Me.txtPatientDOB.Value
End If

lastrow = Worksheets("Procedures").Cells(Rows.Count, 1).End(xlUp).Row
Dim SurgeryTime As String

For i = 2 To lastrow
If (Worksheets("Procedures").Cells(i, 3).Value = pt_DOB Or pt_DOB = "") And Worksheets("Procedures").Cells(i, 2).Value = UCase(patient_name) Then

lblErrorNoPatient.Visible = False
ProcedureID = Worksheets("Procedures").Cells(i, 1).Value
lblProcedureID.Caption = "Procedure ID: " & ProcedureID
Me.txtPatientDOB.Value = Worksheets("Procedures").Cells(i, 3).Value
Me.txtPatientPhone.Value = Worksheets("Procedures").Cells(i, 4).Value
Me.cboPrimarySurgeon.Value = Worksheets("Procedures").Cells(i, 6).Value
Me.cboSecondarySurgeon.Value = Worksheets("Procedures").Cells(i, 7).Value

....

Else

lblErrorNoPatient.Visible = True

End If
Next i

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
August 14, 2020 - 8:52 am
sp_Permalink sp_Print sp_EditHistory

Hi Rebecca,

Can you please supply your workbook, or at least some sample data in the same structure.  I need to run the code and the form against data to see what is not working.

Can you also please elaborate on 'but it is not working on my form' - how exactly?  Is it just that it updates all rows where the patient name is the same or is there more?

Regards

Phil

Avatar
Rebecca Lebwohl

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 14, 2020
sp_UserOfflineSmall Offline
3
August 14, 2020 - 10:58 pm
sp_Permalink sp_Print

*Can you also please elaborate on 'but it is not working on my form' - how exactly?  Is it just that it updates all rows where the patient name is the same or is there more?

It does not find the row when I try to run the code. It just displays the error label.

 

I've attached a sample of the workbook I have been working with for you to use. Many of the fields are blank, feel free to edit as needed.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
August 17, 2020 - 9:47 pm
sp_Permalink sp_Print

Hi Rebecca,

The MATCH function requires an exact match when the last parameter is 0 and in my blog post I used it this way to look up values that I knew were unique, like ID numbers.

To lookup names like you want to do you should use Find and FindNext 

Because your searches can return multiple matches (people with the same name) you need to decide how you want to handle such a situation.  In the attached file I've put a loop in the Lookup() sub that returns every match.  You can either display them all or you could use a drop down list to allow the patient name (and unique identifier?) to be chosen, and avoid having to do this searching altogether.

Hope this points you in the right direction.

Regards

Phil

sp_AnswersTopicAnswer
Answers Post
Avatar
Rebecca Lebwohl

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 14, 2020
sp_UserOfflineSmall Offline
5
August 18, 2020 - 11:04 pm
sp_Permalink sp_Print

Thanks!!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ruth Savage, Andy Kirby, Roy Lutke, Jeff Krueger
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.