Editing Records Through VBA Form

skinfreak

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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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]
Click to expand...

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....
Click to expand...

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
You must log in or register to reply here.

Similar threads

T
  • Solved
Repopulate Form of a Multi Option Button
Replies
3
Views
84
dmt32
D
J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
400
ranman256
ranman256
A
  • Question Question
Record (Row) Locking in Excel
Replies
6
Views
328
Jeffrey Mahoney
Jeffrey Mahoney
W
  • Solved
Passing Multiple parameters to VBA macro from a form button
Replies
7
Views
435
dmt32
D
A
  • Question Question
Excel data from one sheet, copy cells from row i'm working on to excel 'form' based on cell value
Replies
2
Views
566
asiuol84
A

Forum statistics

Threads
1,225,489
Messages
6,185,284
Members
453,285
Latest member
Wullay

Share this page

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