Bren_OG
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
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
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.Valu e) = "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
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.Valu
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
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
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
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
>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
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
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
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
test4-02.xls
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
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
There are now 15 people 'following' me. I'm not sure how it works but there must be something in it for them.
Patrick
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
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
>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
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
Best regards
Brendan
Brendan,
I'm having terrible trouble opening the file. I have no idea what the matter is.
Patrick
I'm having terrible trouble opening the file. I have no idea what the matter is.
Patrick
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
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
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
ASKER
No probs Patrick. I think 4-02 works but i'll do some further testing to make sure.
Best regards
Brendan
Best regards
Brendan
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
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
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
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
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
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
ASKER
Thank you very much Patrick
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.ListCou nt - 1
If UserForm2.ListBox1.Selecte d(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
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.ListCou
If UserForm2.ListBox1.Selecte
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
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
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.ListCou nt - 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
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.ListCou
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
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 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.ListCou nt - 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
Private Sub CommandButton1_Click()
'delete Company details
Dim i As Long
Dim Msg, Style, Title, Response
For i = 0 To UserForm2.ListBox1.ListCou
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
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.
Many thanks
Brendan
Hope you have a good day.
Brendan,
I away until next Sat23Jan - in 5 minutes.
Patrick
I away until next Sat23Jan - in 5 minutes.
Patrick
ASKER
hey!! Have a fantastic and well deserved break!
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
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
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
Please upload your file. Make the data anonymous - BTW we only need a small sample of anonymous data.
Patrick