Link to home
Start Free TrialLog in
Avatar of Bren_OG
Bren_OGFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Edit Excel Database entries with Userform

Hi there
I have created a simple db in excel.  Each row is populated by clicking add entry, this calls up userform1 which holds a variety of text box entries, drop down boxes and option buttons.  Once useform1 is completed and add entry is clicked the entries and values are returned to the next available row on the sheet.  I now want to call up the same useform with a listbox to select the relevant row for edit and update.  The edit userform then populates with the current information in that row for edit.
I would also like an option to delete a row from the edit userform.
I have viewed one thread already but I wasnt sure it was what I needed as I already have the userform built.  I should apologise upfront in that I have very limited knowledge of VB and this is the first userform I have built.  And that is only by trial and error&.sorry.
Really hope you can help given my ignorance.  Any help you can give will be massively appreciated
Best regards
Brendan (Bren_OG)
PS if there is any more info I can provide I will be more than happy to.  Thanks
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Brendan,

Please upload your file. Make the data anonymous - BTW we only need a small sample of anonymous data.

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
I couldn't upload the file as the format is not allowed so I have added the code and a screen shot.  I presume I will have to produce a replica userform that extracts the current data back to the relevant text box etc, just not sure how it's done.
I was hoping to have a date picker on the uderform but after lots of searching for solutions I've kind of given up on that idea.
Many thanks and best regards
Brendan



Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

iRow = ws.Cells(Rows.Count, 2) _
  .End(xlUp).Offset(1, 0).Row

If Trim(Me.Y_NAME.Value) = "" Then
  Me.Y_NAME.SetFocus
  MsgBox "Please enter your name"
  Exit Sub
End If

ws.Cells(iRow, 2).Value = Me.C_NAME.Value
ws.Cells(iRow, 3).Value = Me.C_CON_NM.Value
ws.Cells(iRow, 4).Value = Me.MOB.Value
ws.Cells(iRow, 5).Value = Me.OFFICE.Value
ws.Cells(iRow, 6).Value = Me.EMAIL.Value
ws.Cells(iRow, 7).Value = Me.VET_R.Value
ws.Cells(iRow, 8).Value = Me.VET_PER.Value
ws.Cells(iRow, 9).Value = Me.VET_SCORE.Value
ws.Cells(iRow, 10).Value = Me.PROC_PER.Value
ws.Cells(iRow, 11).Value = Me.EL_DT.Value
ws.Cells(iRow, 12).Value = Me.OptionButton1.Value
ws.Cells(iRow, 13).Value = Me.OptionButton2.Value
ws.Cells(iRow, 14).Value = Me.OptionButton5.Value
ws.Cells(iRow, 15).Value = Me.PL_DT.Value
ws.Cells(iRow, 16).Value = Me.OptionButton6.Value
ws.Cells(iRow, 17).Value = Me.OptionButton7.Value
ws.Cells(iRow, 18).Value = Me.PI_DT.Value
ws.Cells(iRow, 19).Value = Me.OptionButton8.Value
ws.Cells(iRow, 20).Value = Me.OptionButton9.Value
ws.Cells(iRow, 21).Value = Me.OptionButton10.Value
ws.Cells(iRow, 22).Value = Me.VEH_DT.Value
ws.Cells(iRow, 23).Value = Me.OptionButton11.Value
ws.Cells(iRow, 24).Value = Me.OptionButton12.Value
ws.Cells(iRow, 25).Value = Me.OptionButton13.Value
ws.Cells(iRow, 26).Value = Me.Y_NAME.Value
ws.Cells(iRow, 27).Value = Me.DAT_ADD.Value

Me.Y_NAME.Value = ""
Me.C_NAME.Value = ""
Me.C_CON_NM.Value = ""
Me.MOB.Value = ""
Me.OFFICE.Value = ""
Me.EMAIL.Value = ""
Me.VET_R.Value = ""
Me.VET_PER.Value = ""
Me.VET_SCORE.Value = ""
Me.PROC_PER.Value = ""
Me.EL_DT.Value = ""
Me.OptionButton1.Value = ""
Me.OptionButton2.Value = ""
Me.OptionButton5.Value = ""
Me.PL_DT.Value = ""
Me.OptionButton6.Value = ""
Me.OptionButton7.Value = ""
Me.PI_DT.Value = ""
Me.OptionButton8.Value = ""
Me.OptionButton9.Value = ""
Me.OptionButton10.Value = ""
Me.VEH_DT.Value = ""
Me.OptionButton11.Value = ""
Me.OptionButton12.Value = ""
Me.OptionButton13.Value = ""
Me.Y_NAME.Value = ""
Me.DAT_ADD.Value = ""
Me.Y_NAME.SetFocus

End Sub

Private Sub OptionButton2_Click()
If Trim(Me.OptionButton2.Value) = "True" Then
  MsgBox "Employer's liability insurance is a legal requirement unless the contractor is a Sole Trader and does not employ casual or temps"
  Exit Sub
End If
End Sub

Private Sub VET_SCORE_Enter()
VET_SCORE.List = Array("50% - 60%", "61% - 70%", "71% - 80%", "81% - 90%", "91% - 100%")
End Sub

Private Sub close_form_Click()
Me.Y_NAME.SetFocus
UserForm1.Hide
End Sub
screenshot.jpg
Brendan,

Thanks for that, however we can get round the problem of uploading very easily. Save the file as an .xls type file and then upload that.

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
The file is still very much work in progress, so please excuse my crude attempts so far.  I have got so far but havent finished conditional formatting etc as I wanted to be sure I could have editable entries before polishing.  I suppose it might be easier to have one userform that makes new entries and also edits existing ones?
Many thanks
Brendan

test1.xls
Hi Brendan,

I'm dealing with another question at this very moment - the VBA is a bit involved. When I've finished with that I'll start this one.

Patrick
Avatar of Bren_OG

ASKER

OK no problem Patrick.  Thanks for letting me know. :)
Brendan,

I'm on the case. You say "...I have very limited knowledge of VB and this is the first userform I have built." If that's the case then well done - it's good. There are things I would change but I'll come to that later. I'll be back when I've had a little longer to look.

Patrick
Avatar of Bren_OG

ASKER

that's very kind of you to say Patrick, thank you! :)  Yeah i realise there's probably easier ways of doing stuff; any advice you can give will of course be most appreciated.  Thanks again!
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bren_OG

ASKER

Thanks very much!! :¬D  I have to be honest in that i copied all the code from bloggs, i don't know what it means but guess from trial and error and then changed to suit.  I'm an average excel user and that's about it, so praise indeed...
I'll have a look and feedback back ASAP.  I can't thank you enough for your help so far.
Will get back to add some points for you.
Best regards
Brendan
Brendan,

Do please allow me to go back and forth with you on this question before you close it as I'm only too well aware that it's not up to my usual standard of 'finish'.

Patrick
Avatar of Bren_OG

ASKER

It's looking great!! I'm Chuffed to bits.
I'll spend some time testing and see what to do next but it's what I wanted.  If there's any niggly things I'll make a short list if ok.  I would dearly like to come back to a date picker but I'll look at testing first.
Can't believe I haven't come across this resource before, it's brilliant.  I would pay a months perscription based on what you've done so far :¬)
Can I add points now or will it close the question?
I will be working on this over the next few weeks so may not be able to get back to in the next few days or so.
You're a Star!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Brendan,

I've just had a look at your profile. Welcome to Experts Exchange. As you're a newcomer may I give you my standard speil on how EE works - hope you don't mind.

"Perhaps you are not aware of how the points system works for the answerers. It works as follows. Let's assume it's for a question for which 500 points have been offered by the Questioner:  
 
Points offered    Grade         Multiplier      Answerer receives    Cost to Questionner  
500                       A                4                 2000                             500  
500                       B                3                 1500                             500  
500                       C                2                 1000                             500  
                     
It is also important  to point out that as a Premium Service Member you can ask as many questions as you like and offer up to 500 points for a question. As you can see it does not cost you more to award an A. However if you feel that your question has not been answered fully or for example you have only been given a partial answer then a B grade is fair. However it is only right that you say in what way your question has not been fully answered and before you 'Accept' an answer with the intention of awarding a B grade you ensure you have specified in detail what you are needing and that you have given answerers ample opportunity to provide an answer that solves your problem.
 
Please do not leave questions open for ever. If you abandon a question, after 21 days it will be considered as abandoned and will be dealt with by the clean-up volunteers. If a good answer has been given then the points and grade will be awarded to the answerer. So you cannot 'save' points by not 'Accepting' an answer.  Apart from which as a Premium Service Member 'saving' points does not affect the cost of your subscription.
 
On the matter of obtaining good answers, it is very much up to the questioner to respond to comments and to supply further information or feed-back. These are crucial elements to the whole EE process. You will find that the better your feedback the better the answers will be. The answerers are all unpaid volunteers, many but not all of whom work in the IT industry. So they are here because they want to be, not because it earns them a living - because it doesn't!
 
I do hope that helps and that you will accept these comments as just helpful - as that's how they are intended."

Regards

Patrick
disbaled = disabled
Avatar of Bren_OG

ASKER

Hi Stuart
Thank you for the explanation of the points.  I can't believe you do this voluntarily but a massive thanks (as I can't help repeating :¬D)
I have disabled the Submit edit button on userform1 from the code you pointed out.  I put it on the speadsheet 'add contractor button' but it only works after the first open of userform1.  I'll have a play and try and sort it.
I agree that the date picker should be another question and I will get back to that soon hopefully.
Thanks again for the help, support and resolution.  I'll be sure to sing EE's praises at every opportunity.
Kind regards
Brendan
Brendan - Thank you for your kind words and for the grade. I know it's hard to believe that there are popel crazy enough to do this stuff for free, but in fact there's a whole bundle of us here. In fact as I mentioned none of the answerers are paid by Experts Exchange. Well to be honest they sent me a small box of chocolates at Christmastime - and they lasted about 5 minutes! - Patrick
popel = people... oh dear the typos are not reducing...
Brendan,

>I have disabled the Submit edit button on userform1 from the code you pointed out. I put it on the speadsheet 'add contractor button' but it only works after the first open of userform1. I'll have a play and try and sort it.

Do please upload your file and I'll se if I can sort it out.

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
I hope the chocolates were nice at least!  I still can't figure out how you all manage to devote so much time to answering people's questions - and VOLUNTARILY?! Good luck to all of you and I hope that there's some form of personal reward for your efforts!  Don't know how EE maintain your devotion but I can't complain, that's for sure! :¬)
I think I've managed to sort the disable button, i just needed to move the code a line up where I've put it.  I've also added cleardown code to the 'close form' button.  I seemed to be having a few issues with fields still being populated when switching from useform1 to userform2 and vice-versa.  I've put the clear fields into userform2 closure as well.  Well that may well sound like I know what I've done ......well I think it's working.
I wouldn't mind knowing how to add a 'Delete Contractor' button to delete the selected row, if there's a bit of code I can add to a command button I will of course be grateful.
I'm now looking at conditional formatting and return text based on some of the dates that are returned to the sheet. MMM.... more reading I think.
If i ask another question for adding date picker, can I ask you, or is it whoever it comes to.  Based on what I've seen researching I'm sure it will be a good one to look at - it may well already be in the knowledge base.
I've attached the file FYI.
Best regards
Brendan (Big Fan!)
test1-02.xls
Brendan,

I have added a button to UserForm2 - code below. It's in the attached file.

I've not looked at anything else yet.

Patrick
Private Sub CommandButton1_Click()
'delete Company details
Dim i As Long

For i = 0 To UserForm2.ListBox1.ListCount - 1
    If UserForm2.ListBox1.Selected(i) = True Then
        Sheets("Sheet1").Rows(i + 3).EntireRow.Delete
        UserForm2.Hide
    End If
Next i

End Sub

Open in new window

test4-02.xls
Avatar of Bren_OG

ASKER

Wow!! Simply Brilliant!! thank you Patrick.  Think I may start a fan club if you're not careful ;¬)
Brendan,

I think you will find the attached file rather better as I have include code to 'unload' UserForm2. That clears it ready for its next initialisazion.

Patrick
test4-03.xls
Brendan,

There are now 15 people 'following' me. I'm not sure how it works but there must be something in it for them.

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
Sorry for not getting back to you sooner.
I hope the 15 people are 'following' you to give you money or more chocolates at least :¬)
Thanks for adding the extra code - please can you help a little more...userform1 doesn't appear when the a contractor is selected for edit from useform2?  I've had a look but can't see where anything may be missing.
Thanks and best regards
Brendan
Brendan,

>I hope the 15 people are 'following' you to give you money or more chocolates at least :¬)

Nope, absolutely zilch. But I learn from helping so that's a bonus.

I'll have a look at the issue.

Patrick
Avatar of Bren_OG

ASKER

That's very valiant of you Patrick from where I'm standing.  Thanks as always.  I will do some further 'additions' if can i can over the next week or so.  It's funny that now I've started I can think of a variety of things and rules I would like to add but I could get carried away...it's quite additive once started...but I know my limits :¬D
Best regards
Brendan
Brendan,

I'm having terrible trouble opening the file. I have no idea what the matter is.

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
I've tried adding the 4-03 file to see if you can open that one?  Thanks as always :)
Brendan
test4-03.xls
Brendan,

There is something pretty dire with that file. It has now crashed my Excel about 6 times. I know it's a pain but may I ask you to have a look at the attached file and let me know what needs to be done - at least it works for me at the moment.

Patrick
test4-02.xls
Avatar of Bren_OG

ASKER

No probs Patrick. I think 4-02 works but i'll do some further testing to make sure.
Best regards
Brendan
Avatar of Bren_OG

ASKER

Hi again Patrick
I hope you are well and not working too hard?
I have what is hopefully a simple question - where userform1 selects the next available row,
iRow = ws.Cells(Rows.Count, 2) _
  .End(xlUp).Offset(1, 0).Row
can I stipulate the code to ignore the contents of the first column?  Basically if I have conditional formatiing based on DATEDIF down the first column and the userform populates the next empty row it takes the formula into account.  Alternatively, would be it be easier to put the DATEDIF formula in from userform1?
Many thanks and kind regards as always.
Brendan
Brendan,

You've got me beat because:

irow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

only identifies the first free row in column "B".  It doesn't even look at the first column "A".

BTW, what is this line of code in Private Sub CommandButton2_Click() meant to do?

irow = UserForm2.editrow + 3

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
Yeah the issue I spoke of seems intermittent.  I had no entries other than in column A and adding a new contractor started on the 4th or 5th row down when the rows above were blank.  However Ive tried again and it seems to work ok.  The only thing (and Im clutching at straws) is that the edit contractor button showing userform2 shows a number of blank entries before the first populated row, as if it hasnt quite cleared down since the last entries were deleted.  Then when reopening userform2 the blank entries are gone?  Anyway Ill continue with conditional formats etc and test again.
As for the code you mentioned irow = UserForm2.editrow + 3, I dont know what it is, maybe something Ive copied from what you did.  In the context of where it is and from your question, I presume I can get rid of it?
I really dont want to keep bothering you with niggly stuff so Ill work on and see how I do.
Best regards and thanks as always J
Brendan
Brendan,

I'm OK with continuing to attempt to help - so don't worry about getting me or others involved with what you term 'niggling' matters.

Patrick
Avatar of Bren_OG

ASKER

Thank you very much Patrick
Avatar of Bren_OG

ASKER

Hi Patrick, it's me again.  How are you?  Well I hope.  I have 'man flu' ...boo
I added some additional columns at the start of the DB.  I have adjusted code accordingly in most cases but I seem to have a problem with userform2 not showing all available entries that can be edited and the one it does show is not the right row details when selected to edit?
Also, I have tried to create a warning message box for deleting the selected row, I'm obvioulsy missing a bit of instruction to get it to run or I have it completely wrong?  As below + I have attached latest file version:
Private Sub CommandButton1_Click()
'delete Company details
Dim i As Long

For i = 0 To UserForm2.ListBox1.ListCount - 1
    If UserForm2.ListBox1.Selected(i) = True Then MsgBox("Are you sure? Have you Selected the correct details to delete?" & Chr(13) & _
    Chr(13) & _
    " - Click 'Yes' to Permanently Delete Details." & Chr(13) & _
    " - Click 'No' to go back to select a Company.", 52, "Delete Company Details?")
    If returnvalue = vbYes Then Sheets("Sheet1").Rows(i + 3).EntireRow.Delete
        UserForm2.Hide
    End If
Next i

End Sub

I have also deleted the line of code you mentioned previously, don't suppose that's got anything to do with showing editable lines from userform2.
Sorry I'm so clueless!
Best regards
Brendan
test4-02-01.xls
Brendan,

I've been out all day - thus the delay.

>I seem to have a problem with userform2 not showing all available entries that can be edited and the one it does show is not the right row details when selected to edit?

To deal with that use this for UserForm2:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rng As Range
Dim celle As Range
Dim i As Long

Set ws = Sheets("Sheet1")
Set rng = ws.Range(ws.Cells(3, "E"), ws.Cells(ws.Rows.Count, "E").End(xlUp))
For Each celle In rng
    UserForm2.ListBox1.AddItem celle
Next celle
End Sub

The problem was with ws.Cells(6, "E") - it needs to be ws.Cells(3, "E")

I'll be back with the Msgbox issue.

Patrick
Brendan,

The MsgBox issue can be dealt with with this code:

Private Sub CommandButton1_Click()
'delete Company details
Dim i As Long
Dim Msg, Style, Title, Response

For i = 0 To UserForm2.ListBox1.ListCount - 1
    Msg = "Are you sure you have you selected the correct details to delete?" ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Delete details?"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then    ' User chose Yes.
        Sheets("Sheet1").Rows(i + 3).EntireRow.Delete   ' Perform some action.
        Unload UserForm2
    Else    ' User chose No.
        Unload UserForm2   ' Perform some action.
    End If
Next i

End Sub

Hope that helps

Patrick
Avatar of Bren_OG

ASKER

Hi Patrick
Sorry for not getting back to you sooner.  Thanks very much for the solutions.  Thought I was doing the right thing regarding Userform2, what a numpty!! Sorry.
Thanks for the advice re the message box.  Please can you tell me how to -
If Response = vbNo Then 'close msgbox and leave userform2 open'
Best regards
Brendan
Nearly there....btw where are you based UK, US?
Sorry, v. short of time. Like this...

Private Sub CommandButton1_Click()
'delete Company details
Dim i As Long
Dim Msg, Style, Title, Response

For i = 0 To UserForm2.ListBox1.ListCount - 1
    Msg = "Are you sure you have you selected the correct details to delete?" ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Delete details?"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then    ' User chose Yes.
        Sheets("Sheet1").Rows(i + 3).EntireRow.Delete   ' Perform some action.
    End If
Next i

End Sub

Patrick
Avatar of Bren_OG

ASKER

Thanks Patrick.  I had removed the unload before but I have to click the 'no' button 3 times to close the msgbox.  Don't worry if you're busy.
Many thanks
Brendan
Hope you have a good day.
Brendan,

I away until next Sat23Jan - in 5 minutes.

Patrick
Avatar of Bren_OG

ASKER

hey!! Have a fantastic and well deserved break!
Avatar of Bren_OG

ASKER

Hi Patrick

I hope you had a nice break?
Is it possible for me to open another question and direct it to you for a couple of things related to what you have kindly done so far?
Best regards
Brendan
Brendan,

We had an excellent break thank you.

You can ask another question but it can't be 'aimed' at only me. The way to do it is to ask a new question and give a link to this question and in this question give a link to the new question. I will pick up the link you give in this question automatically (via email) but I won't necessarily see the new question - depends on whether I'm looking at EE or not.

Patrick