VBA code

[eabaker64]

Hi. I have created a user form so an individual can enter a date and enter a comment. When "Press enter to save comments" is pressed, the date goes to cell G51 and the comments go to cell G52. I would like the most current date/comment to be shown in G51/G52, and older comments will be moved down to the next row. What VBA code would I use to do this?

 

[Ezguy4u]

eabaker here is a solution to your problem. Just make sure the code is before the user form

Range("G51:G52").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

 

[eabaker64]

eabaker here is a solution to your problem. Just make sure the code is before the user form

Range("G51:G52").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Thank you for that. Works great. One more question: Can you input text from a Userform into a merged cell? Or is there code that can be used to merge the cells once the data has been input...maybe code that does the formatting right before exiting the form.

 

[Ezguy4u]

eabaker Run this code and see if it's close to want you want. If it is a solution to your question than next step would be to fit it into a Userform. Yes you can input text into a merged form. I am going to say you shouldn't merge cells with data in it.

Sub Program2()
'
    Range("A1:B1").Select
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Now is the time"
    Range("A2").Select
End Sub

 

[eabaker64]

eabaker Run this code and see if it's close to want you want. If it is a solution to your question than next step would be to fit it into a Userform. Yes you can input text into a merged form. I am going to say you shouldn't merge cells with data in it.

Sub Program2()
'
    Range("A1:B1").Select
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Now is the time"
    Range("A2").Select
End Sub

Thank you!! I think I only have one last question. The code for the user form ends up with text being formatted in merged cells A13:D13 so that it autofits the cell (lots of interesting code there). When text is entered into A13:D13, per your handy solution above, the text that had been there before gets shifted down to the next cell. However, once it is shifted down into the next cell, it loses the format that it had when it was in cell A13:D13. Meaning, it now only shows the standard cell of 15 high instead of the autofit from above. How can I keep the format the same?

 

[Ezguy4u]

eabaker I am going to say there is probably a few different ways to get those cells formatted. Here is mine. Now, notice font name and font size. Adjust those as needed. So if you have any questions, problems or comments, let us know.

Sub Prog3()
Range("A13:D13").Select
Selection.Insert Shift:=xlDown
Selection.Font.Name = "Aptos Narrow"
Selection.Font.Size = 36
Columns("A:D").Select
    Columns("A:D").EntireColumn.AutoFit
    Rows("13:13").Select
    Rows("13:13").EntireRow.AutoFit
    Range("A13").Select

End Sub