Target Row Question

mhardage

New Member
Joined
Oct 28, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I have successfully incorporated an EDIT button in Excel/VBA to find a target row and return the completely filled out user form so the user can make edits. The code is below, which I copied from Chris Mortimer to create my database.

My question is how can I use this same method to create an Administrative Edit Button that uses a different criteria (value in different column) to pull the user form up so I can edit based on a TripNumber. I have the button and it pulls up my trip #'s to search just like my successful code, but does not populate the user form so I can edit based on the TripNumber

Successful code for User Form....

VBA Code:
Option Explicit
Private Sub CommandButton_Continue_Click()
'When we click the 'continue' button

Dim TargetRow As Integer 'variable for position control 'user target row'
Dim TripFullName As String 'trip full name
Dim TripNumber As String 'trip number
Dim UserMessage As String 'variable to configure user message at the end

TripFullName = TextBox_Date & " " & ComboBox_SportEvent & " " & ComboBox_TeamGroup 'concatenate Date, SportEvent and TeamGroup for use in code below
TripNumber = TextBox_TripNumber 'Admin search component

'begin check if in 'edit' or 'add new' mode
If Sheets("Engine").Range("B4").value = "NEW" Then 'in 'new' mode

    'begin validation check 'check if name already exists
    If Application.WorksheetFunction.CountIf(Sheets("Data").Range("F11:F1011"), TripFullName) > 0 Then
       
    MsgBox "Name already exists", 0, "Check"
    Exit Sub 'notify user and exit the routine
   
    End If
    'end validation check
   
TargetRow = Sheets("Engine").Range("B3").value + 1 'make variable equal to COUNTA formula on worksheet + 1
UserMessage = " has been added to the database" 'configure user message for add new entry

Else 'in 'edit' mode

TargetRow = Sheets("Engine").Range("B5").value 'make variable equal to the value saved in the engine
UserMessage = "'s details have been edited" 'configure user message for edit entry

End If
'end check if in 'edit' or 'add new mode'


'''BEGIN INPUT DATA INTO DATABASE'''



Successful code for the User Edit Button....

VBA Code:
Option Explicit

Private Sub CommandButton_User_Search_Click()

Dim TargetRow As Integer 'Variable to save position of this Entry in databse

'use Match worksheet function to find position of chosen entry
TargetRow = Application.WorksheetFunction.Match(ColumnF_Menu, Sheets("Data").Range("Dyn_Trip_Full_Name"), 0)
Sheets("Engine").Range("B5").value = TargetRow 'save position in the engine, for use later


My new edit criteria would be ColumnU_Menu & "Dyn_Trip_Number"

Unload Find_Entry_UF 'unload the userform to select a name

'''Begin retrieving data from database'''
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,128,025
Messages
5,628,192
Members
416,300
Latest member
Kxs00301

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
Top