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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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
 
Upvote 0
[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.
 
Upvote 0
Am I best off putting this in as a public module or directly in the form code? i am having trouble activating it....
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,079
Members
449,988
Latest member
Mabbas

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