VBA for stepwise process involving dialog boxes with fillable fields

[michaeltsmith93]

Hi,

I will preface this by saying that I'm new to VBA. I'd like to write some code for a command button that will complete a series of actions and then create a series of dialog boxes with fields that the user can then type in, and then when you close the final dialog box, all of those things will happen. Is this possible? If so, could someone point me toward a post that deals with something similar? I don't want someone to just write it for me, and I'm not sure what search terms to use to find some similar code.

These are the things that I want to incorporate:

1. Find last row of data that contains any text.
2. Copy selection A6:AP6 and paste to row below last row.
3. Dialog box asking for "LastName FirstName" whose value will go in Column A of this row.
4. Dialog box asking for "Study Role" whose value will go in Column B of this row.
5. Dialog box asking for "Matrix Role" whose value will go in Column D of this row.
6. Execute these steps.

I think I can figure out steps 1 and 2 on my own. I just need some guidance regarding steps 3-6. A link to another forum post would be great! I also don't mind if it's a series of dialog boxes or one box with multiple fields to populate.

Thank you!
Michael

 

[Fluff]

I'd recommend a userform, rather than 3 separate inputboxes.
Have a look here https://www.contextures.com/xlUserForm01.html

 

[michaeltsmith93]

This is exactly what I was looking for! Thank you so much, Fluff. While I have you on hand, I'm working on steps 1 and 2 at the moment and hitting a snag. Can you see what the issue is with my code? Sorry for the plain text.



Private Sub CommandButton1_Click()


Dim Lrow As Long


Lrow = Cells.Find(What:="", _
After:=Range("A6"), _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row




Range("A6:AP6").Copy Range("A" & Lrow)


End Sub

 

[Fluff]

Is there any specific column that will always have data in the last row?

 

[michaeltsmith93]

Column A will. I’d like it to start looking in A6, hence my use of Range.Find. Thoughts?

 

[Fluff]

In that case I'd do it like

Dim NxtRw As Long
NxtRw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Range("A6:AP6").Copy Range("A" & NxtRw)

or another way

Range("A6:P6").Copy Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[michaeltsmith93]

Thank you, Fluff. I like the second method a lot.

I will comment back here when I inevitably run into issues with the UserForm.

 

[Fluff]

If you run into any problems, it's best to start a new thread each time.