Editing Records Through VBA Form

skinfreak

Board Regular
Joined
May 23, 2003
Messages
152
I have designed a nice VBA form that seems to work well but the powers that be have changed the spec. Initially the data entry was done all at once, but now they want to do this in stages.

My form features 4 tabs, and this corresponds more or less to the four stages of data entry (this was not intentional). What I need to do is give the user the ability to check if the record has been started and if so, append it.

Essentially I need a query to check for a customer number that is in Column A, and display data from this row to make the decision of either creating a new query or editing the current one (ie. data from certain other cells on that row).

It more or less needs to behave like Access with possibly forward and back arrows, but unfortunately Access is out of the question (£$£$!).

Am I on a loser with this? Are record search arrows possible in VBA, or should I scrap it and tell them to use Access? Anyone's ideas with this would be treated like a God for evermore...I don't want to bin what I've done but at the end of the day I'm not going to waste more time in case they change what they want again!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
sf.

it wouldn't be too hard to search a worksheet for a number, and then activate that row if found. something like the below code would work. i'm not sure what you mean, though, by forward and back arrows?

cheers. ben.

Code:
Sub Test()
    Dim strID As String
    
    strID = "123"
    Call FindID(strID)
    
End Sub
Private Sub FindID(MyID As String)

    Dim rngSearch As Range, c As Range
    
    Set rngSearch = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
    
    Set c = rngSearch.Find(What:=MyID, LookIn:=xlValues, LookAt:=xlWhole)
    
    If c Is Nothing Then
        MsgBox "ID not found."
    Else
        c.EntireRow.Activate
    End If
       
'   Empty object variables
    Set rngSearch = Nothing
    Set c = Nothing
    
End Sub
 

skinfreak

Board Regular
Joined
May 23, 2003
Messages
152
In Access, you can search between records with forward and back navigation arrows. That's what I meant.

Regarding your code, if the row is activated, how do I load the current values into the user form? Is it the inverse of pulling the values into the spreadsheet upon save?

EG:
Code:
ActiveWorkbook.Sheets("Path2005").Activate

    Range("A1").Select

    Do

    If IsEmpty(ActiveCell) = False Then

        ActiveCell.Offset(1, 0).Select
    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = txtSurName.Value
    
    ActiveCell.Offset(0, 1) = txtForeName.Value
    ActiveCell.Offset(0, 2) = txtDOB.Value
    ActiveCell.Offset(0, 3) = txtAddress1.Value
    ActiveCell.Offset(0, 4) = txtAddress2.Value
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
[snip]

Regarding your code, if the row is activated, how do I load the current values into the user form? Is it the inverse of pulling the values into the spreadsheet upon save?

[snip]

yes, exactly right. so if i have a textbox named txtExample, then i can "load" this from a cell with

Code:
txtExample.Value = ActiveCell.Value

if you are really interested in loading certain cell values into the form, rather than actually showing the user the row in the worksheet, then i would skip the activation step (for example only) and just reference your values from your found cell c:

Code:
txtExample.Value = c.Value
txtExample2.Value = c.Offset(0,1).Value
etc

ben.
 

skinfreak

Board Regular
Joined
May 23, 2003
Messages
152

ADVERTISEMENT

Oh My God. This is rather cool. :D
 

skinfreak

Board Regular
Joined
May 23, 2003
Messages
152
Am I best off putting this in as a public module or directly in the form code? i am having trouble activating it....
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345

ADVERTISEMENT

You should put the code in the form initialize event (or activate), sometimes excel seems to fritter between calling the two if the form hasn't been explicitly unloaded.


alternatively you could put it in a module and call the procedure with the arguments in the form.
 

skinfreak

Board Regular
Joined
May 23, 2003
Messages
152
You cannot put subroutines in the initialize section can you?

Code:
Private Sub UserForm_Initialize()

Sub Test()
    Dim strID As String
   
    strID = "123"
    Call FindID(strID)
End Sub

Private Sub FindID(MyID As String)

    Dim rngSearch As Range, c As Range
   
    Set rngSearch = Intersect(Columns("C:C"), ActiveSheet.UsedRange)
   
    Set c = rngSearch.Find(What:=MyID, LookIn:=xlValues, LookAt:=xlWhole)
   
    If c Is Nothing Then
        MsgBox "ID not found."
    Else
        'c.EntireRow.Activate
        txtSurName = c.Offset(0, -2).Value
        txtForeName.Value = c.Offset(0, -1).Value
        txtID.Value = c.Value
End Sub

I get a compile error "Expected End Sub".
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
sf.

Am I best off putting this in as a public module or directly in the form code? i am having trouble activating it....

you should call it from your initiatize event:

Code:
Private Sub Userform_Initialize()
    Call Test
End Sub

and place Test and FindID in a standard module.

ben.
 

skinfreak

Board Regular
Joined
May 23, 2003
Messages
152
OK. I created a new module called search, and placed Call Test in the initialize. When I run the form i get the "ID Not Found" message.

Now, the code that has been provided doesn't include a prompt does it? So I need to include a prompt to fill the StrID string in the initialise statment too, don't I?
 

Forum statistics

Threads
1,141,847
Messages
5,708,934
Members
421,599
Latest member
santosh234

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