Userforms and copying to ss (bound to be a re-post)

[RBartonSWW]

I am writing a VBA Userform for my work, I've learnt most of the stuff I need to learn for it over 7 and a half hours at work today, I've completed the search and auto fill function that I need it to do.
So at the moment I enter a client's account number press search and it looks it up and pulls all their details into the correct boxes for easy view...that's fine.

What I am trying to do is now code a button so that when I edit some of the client's details and press 'Update' it updates the info on the spreadsheet. I can't figure out how to get it to copy the data from the TextBox on the UserForm to the spreadsheet. Once I know how to get it onto the spreadsheet I can easily code it to make it go to the corresponding cell.

I would really like to finish this over the weekend because my boss is coming back on Monday and I want it done for then.

Cheers

 

[RBartonSWW]

This is the code so far for the UserForm. As you can see the 'Update' button doesn't have anything in it except for the comment saying what it does (will do).

Please can someone help this is really important.





Private Sub cmbCancel_Click()

tbAcc.Value = ""
tbName.Value = ""
tbSnt.Value = ""
tbRtn.Value = ""
tbRef.Value = ""
tbChsd.Value = ""
frmSearch.Hide

End Sub

Private Sub cmbSearch_Click()
Dim firstCell, nextCell, stringToFind As String
' Show an input box and return the entry to a variable.
stringToFind = tbAcc.Value
Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlWhole, _
searchdirection:=xlPrevious)
' If the string is not found, show this message box.
If firstCell Is Nothing Then
MsgBox "Claim Not Found.", vbExclamation
Else
' Otherwise, find the next occurrence of the search text.
nextCell = _
Cells.FindNext(after:=Range(firstCell.Address)).Address

Range(firstCell.Address).Select
Selection.Offset(0, 1).Select
ActiveCell.Copy
tbName.Paste
'Puts data in the name field on the spreadsheet onto the macro form

Range(firstCell.Address).Select
Selection.Offset(0, -1).Select
ActiveCell.Copy
tbRef.Paste
'Puts data in the Rapid Reference field on the spreadsheet onto the macro form

Range(firstCell.Address).Select
Selection.Offset(0, -3).Select
ActiveCell.Copy
tbSnt.Paste
'Puts data in the Date Sent field on the spreadsheet onto the macro form

Range(firstCell.Address).Select
Selection.Offset(0, -2).Select
ActiveCell.Copy
tbRtn.Paste
'Puts data in the Date Returned field on the spreadsheet onto the macro form

Range(firstCell.Address).Select
Selection.Offset(0, 2).Select
ActiveCell.Copy
tbChsd.Paste
'Puts data in the Date Chased field on the spreadsheet onto the macro form

Range(firstCell.Address).Select
'Moves slection back to the active cell, allowing for re-use of Offset for Update

End If

End Sub

Private Sub cmbUpdt_Click()


'Moves slection back to the active cell, allowing for re-use of Offset for Update


End Sub

 

[colinharwood]

Hi

Don't know if this is any use to you , but this is the code I use to transfer data from a userform to the spreadsheet


lastrow = Range("A200").End(xlUp).Offset(1, 0).Row

Range("A" & lastrow).Value = DataEntry.EntryNumber1.Text
Range("B" & lastrow).Value = DataEntry.EntryNumber2.Text
Range("C" & lastrow).Value = DataEntry.Category.Text

where Dataentry is the name of the userform & entrynumber and category are textboxs on the form

Hope this helps

Colin

 

[RBartonSWW]

Cheers Colin.

Unfourtunatly it doesn't appear to be what I'm after, the main problem is the fact that I don't have a set cell that it is updating the info into. It is different depending on the Claim Number I search for.

Here is an Image of the front end and the spreadsheet if it'll help people understand and be able to help me.



The data in the spreadsheet is all fake data as I am at home and for legal reasons I can't have the real data, but it should work for any data I enter into the spreadsheet and front end Userform.

Cheers all.

Rob

 

[HalfAce]

Hello RBartonSWW, welcome to the board.
Colin's example is going to work well providing all cells in each row of your data table have values.
However, if some cells are left blank you might consider using an offset method. For example:

lastrow = Range("A200").End(xlUp).Offset(1, 0).Row 

Range("A" & lastrow).Value = DataEntry.EntryNumber1.Text
Range("A" & lastrow).Offset(, 1).Value = DataEntry.EntryNumber2.Text 
Range("A" & lastrow).Offset(, 2).Value = DataEntry.Category.Text

etc.

Another way of doing the same thing would be:

Range("A" & lastrow).Value = DataEntry.EntryNumber1.Text
Range("A" & lastrow)(1, 2).Value = DataEntry.EntryNumber2.Text
Range("A" & lastrow)(1, 3).Value = DataEntry.Category.Text

This all assumes of course you want your updates to be added to the bottom of your existing data table. If you're actually editing some of the data/cell(s) in your existing data then you'll want to employ your search code to locate the row you want to update.

Hope it helps.

 

[HalfAce]

OK, here's an example of what you can do. (Didn't see your last post when I posted.)
This assumes the textbox you're entering the account number to search for is named "ClientAccntNum" (Change that to suit.)
It also assumes your account numbers to search through are in column A of the active sheet, and you want the value for EntryNumber1 to go in column B, EntryNumber2 to go in column C & Category to go in column D, etc.

Dim fCell As Range, Rng As Range
Dim LstRw As Long
LstRw = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(Cells(1, 1), Cells(LstRw, 1))
Set fCell = Rng.Find(ClientAccntNum, lookat:=xlWhole)
If Not fCell Is Nothing Then
    If Len(DataEntry.EntryNumber1) > 0 Then fCell(1, 2).Value = DataEntry.EntryNumber1.Text
    If Len(DataEntry.EntryNumber2) > 0 Then fCell(1, 3).Value = DataEntry.EntryNumber2.Text
    If Len(DataEntry.Category) > 0 Then fCell(1, 4).Value = DataEntry.Category.Text
Else: MsgBox "The account for " & ClientAccntNum & " can not be found."
End If

This will replace the values for only textboxes that are not blank, (so you only have to fill in what you want changed) and will tell you if the account number being searched for is not found.

Hope it helps.

 

[RBartonSWW]

Man that's alot of coding for me to read and digest.

Ok, I may not have made it hugly clear but basicaly I want to pull up the information on say Claim Number 36985214. So I enter that into the thing I have so far and press search, that then automatically fills the Rapid Ref. with the Account Number and the Name and dates. That's all fine.

But say I'm checking my paperwork and I see that the 'Chased' date is infact 20/08/05 then I will want to change that and rather than trawl through thousands of records to find the one I want I can easily update it from this by entering 20/08/05 into the 'Chased' textbox and press 'Update' for it to then change the date on the spreadsheet to the 20/08/05 instead of the 15/08/05. - Well that's the theory.

I've gotton my search code done and dusted...if it is a little squewy the way it works.

Effectivly I want it to copy all the data in the Userform into the spreadsheet in the correct places when I press update. Looking at the code that has been given to me by you guys I should be able to do is.
I didn't fully understand what Colin meant as I thought he meant that it would put the data into certain cells which is not what I need, it has to be the same as the ones it has found the data in.

Cheers all of you guys...no doubt I'll be back...hopefuly to say thanks properly...but being as new as I am...probably for more help.

 

[RBartonSWW]

    If Len(DataEntry.EntryNumber1) > 0 Then fCell(1, 2).Value = DataEntry.EntryNumber1.Text
    If Len(DataEntry.EntryNumber2) > 0 Then fCell(1, 3).Value = DataEntry.EntryNumber2.Text
    If Len(DataEntry.Category) > 0 Then fCell(1, 4).Value = DataEntry.Category.Text
Else: MsgBox "The account for " & ClientAccntNum & " can not be found."
End If

The idea of that seems to work, but the problem I have now is what is 'DataEntry.EntryNumber1' (etc.) ? I assumed that I wanted it to be 'DataEntry.tbSnt' (for the 'Sent' text box) but that didn't work.

This is probably some of the basic stuff that I am asking now, but that's what happens if you don't do a course and jump in at the deep end
*mental note to self - do a course before agreeing to write code*
If you could expand on that and explain what I have asked that would be really appreciated, thanks.

Thankyou all...again.

 

[HalfAce]

OK, I have a little better understanding of what you're after.
(but only a little better)

what is 'DataEntry.EntryNumber1'?

This is just an example meaning "DataEntry" is the name of your userform and "EntryNumber1" is the name of your textbox.
If the name of the userform is actually "DataEntry" and the textbox of interest is named "tbSnt" then your assumption would be correct.

Now, if you want to be able to search for "anything" in your sheet by entering it into a textbox (and hitting a button) then I'd need to know a few things about your setup.
First of all (to search for anything), you'll want to search the entire sheet instead of just one column like the example I posted above.
For that you would delete the line:
Set Rng = Range(Cells(1, 1), Cells(LstRw, 1))
and then change the line:
Set fCell = Rng.Find(ClientAccntNum, lookat:=xlWhole)
to:
Set fCell = Cells.Find(ClientAccntNum, lookat:=xlWhole)

Now, this still assumes you're going to be typing your "search for" value into the same textbox, no matter what you're searching for as we're using the value in a specified textbox (ClientAccntNum) to search for.
If you want to use (for example) a date textbox to search for a date or an Accnt Number textbox to search for an account number, a name textbox to search for a name, (etc.) then in your update button's code you'll need to test for which textbox has a value in it, to determine which textbox to use in your "Cells.Find(TextBox Name Here, lookat:=xlWhole) line.
And then you'll need some code to determine where the offsets will be depending on what column the searched for value is going to be found in.

(It all sounds worse than it really is.)
This probably doesn't answer all your questions, but one step at a time is pretty good too.

See if you can make this make any sense to you and then post back when you're ready to move on.

(If worse comes to worse, you can PM me for my email address and I'll take a look at what you've got.)
Dan

 

[RBartonSWW]

Cheers for the explanation, I have managed to re-code what you gave me to fill out the correct boxes. Now I think I will create a new Userform that will allow me to create new accounts on the spreadsheet. Shouldn't be too hard now that I have the copying from Userform working.

Although I will need to code it to search for empty rows and input into them...that should require some help from you nice people.

I think someone recently posted asking about blank cell finding, but I'm after rows...which technically shouldn't be too much different or that difficult.

To get the new Userform to open what I want the user to do is to enter the Claim Number into the Search dialogue box and then if there is no hit on it a message box comes up saying 'No claim number found. Would you like to add new to spreadsheet?" "Yes/No" The actual text doesn't matter but what I want to so is use a Yes/No msgbox to open the new Userform if the user presses 'Yes' and to close the message box and nothing else if the user presses 'No'.
What I would also like to do is remove the 'X' in the top bar of the forms so that the user cannot access the spreadsheet other than via the Userforms. - This isn't nesicary but would be helpful if I could do it.


Again, thank you for the help so far, and any more on the new idea I've had would be greatly appreciated