Every time the button is pressed output the values row below.

[rebeila]

Hello!

I have a VBA code that consists of some msgboxes and input boxes. Basically when you press the button and the macro runs the values are inserted on different worksheets. Instead of having many buttons, I want one button that will have the same code but insert one row below.

Any Ideas_

 

[Joe4]

The devil is always in the details, but you really have not provided those. Please show us your VBA code, a sample of your data, and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[rebeila]

The devil is always in the details, but you really have not provided those. Please show us your VBA code, a sample of your data, and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I can not show you my sample data but my expected output is that range would go one row down. for example C6

Here is my code:

Sub MsgBoxYesNo()

Range("'Data2'!$C5") = "=NOW()"

a = InputBox("Box ")
Range("'Data2'!$E5") = a

Ans = msgbox("ALL Good?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
Range("'Data2'!$F5").Value = "Pass"
Case vbNo
Range("'Data2'!$F5").Value = "Fail"
End Select

Ans = msgbox("Floor Undamagaed?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
Range("'Data2'!$G5").Value = "Pass"
Case vbNo
Range("'Data2'!$G5").Value = "Fail"
End Select

Ans = msgbox("Correct temp?", vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Range("'Data2'!$H5").Value = "Pass"
Case vbNo
Range("'Data2'!$H5").Value = "Fail"
End Select

Ans = msgbox("water full?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
Range("'Data2'!$I5").Value = "Pass"
Case vbNo
Range("'Data2'!$I5").Value = "Fail"
End Select
Ans = msgbox("humidity ok?", vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Range("'Data2'!$J5").Value = "Pass"
Case vbNo
Range("'Data2'!$J5").Value = "Fail"
End Select

Ans = msgbox("Quantity OK?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
Range("'Data2'!$K5").Value = "Pass"
Case vbNo
Range("'Data2'!$K5").Value = "Fail"
End Select

b = InputBox("Type of Quality test")
Range("'Data2'!$L5") = b

c = InputBox("Remarks")
Range("'Data2'!$M5") = c

D = InputBox(" Name")
Range("'Data2'!$O5") = D

 

[Joe4]

So assuming that your data is to start on row 5, and then go down one row each time after that, you can change the start of your code like this to calculate where the next row of data is:

Sub MsgBoxYesNo()

Dim ws as Worksheet
Dim nextRow as Long

Set ws = Sheets("Data2")

'Find next row of data by finding last row with data in column C and moving down one row from that
nextRow = Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
If nextRow < 5 Then nextRow = 5

ws.Cells(nextRow, "C") = "=NOW()"

a = InputBox("Box ")
ws.Cells(nextRow, "E") = a
...

I showed you how to change the range references for the first two (column C and E). Just use the same logic for the rest of your code.

 

[rebeila]

So assuming that your data is to start on row 5, and then go down one row each time after that, you can change the start of your code like this to calculate where the next row of data is:

Sub MsgBoxYesNo()

Dim ws as Worksheet
Dim nextRow as Long

Set ws = Sheets("Data2")

'Find next row of data by finding last row with data in column C and moving down one row from that
nextRow = Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
If nextRow < 5 Then nextRow = 5

ws.Cells(nextRow, "C") = "=NOW()"

a = InputBox("Box ")
ws.Cells(nextRow, "E") = a
...

I showed you how to change the range references for the first two (column C and E). Just use the same logic for the rest of your code.

I have tried the code but it does not output one row bellow, it just overwrites on the same row

 

[Joe4]

I have tried the code but it does not output one row bellow, it just overwrites on the same row

Did you update EVERY single reference to row 5 in your code with my range references?
Please post your complete updated code.

If you did that, and it is still not working properly, please show us the structure of your "Data2" sheet.
If you have any sensitive data in it, just replace it with "dummy" data.

 

[rebeila]

Did you update EVERY single reference to row 5 in your code with my range references?
Please post your complete updated code.

If you did that, and it is still not working properly, please show us the structure of your "Data2" sheet.
If you have any sensitive data in it, just replace it with "dummy" data.

Sub MSGBOXWEE()


Dim ws As Worksheet
Dim nextRow As Long

Set ws = Sheets("Data2")

nextRow = Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
If nextRow < 5 Then nextRow = 5

ws.Cells(nextRow, "C") = "=NOW()"

a = InputBox("Box")
ws.Cells(nextRow, "E") = a

Ans = msgbox("Label OK?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
ws.Cells(nextRow, "F").Value = "Pass"
Case vbNo
ws.Cells(nextRow, "F").Value = "Fail"
End Select

Ans = msgbox("Undamagaed?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
ws.Cells(nextRow, "G").Value = "Pass"
Case vbNo
ws.Cells(nextRow, "G").Value = "Fail"
End Select

Ans = msgbox("CP?", vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
ws.Cells(nextRow, "H").Value = "Pass"
Case vbNo
ws.Cells(nextRow, "H").Value = "Fail"
End Select

Ans = msgbox("Full?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
ws.Cells(nextRow, "I").Value = "Pass"
Case vbNo
ws.Cells(nextRow, "I").Value = "Fail"
End Select
Ans = msgbox("Undamaged?", vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
ws.Cells(nextRow, "J").Value = "Pass"
Case vbNo
ws.Cells(nextRow, "J").Value = "Fail"
End Select

Ans = msgbox("Quantity OK?", vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
ws.Cells(nextRow, "K").Value = "Pass"
Case vbNo
ws.Cells(nextRow, "K").Value = "Fail"
End Select

b = InputBox("Type of Quality test")
ws.Cells(nextRow, "L") = b

c = InputBox("Remarks")
ws.Cells(nextRow, "M") = c




D = InputBox("Name")
ws.Cells(nextRow, "O") = D

End Sub

 

[Joe4]

Is the image you posted above of the "Data2" sheet?
Are your dates shown above really in column C?
Is row 5 the first row with data in that column?

Add this line to the code, and see what it returns when you run it two times:

nextRow = Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
If nextRow < 5 Then nextRow = 5
MsgBox nextRow

 

[rebeila]

Is the image you posted above of the "Data2" sheet?
Are your dates shown above really in column C?
Is row 5 the first row with data in that column?

Add this line to the code, and see what it returns when you run it two times:

nextRow = Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
If nextRow < 5 Then nextRow = 5
MsgBox nextRow

Yes DATA2 sheet
Yep in column C
No, C4 has text
I have added the code but it does not work.

 

[Joe4]

I have added the code but it does not work.

What do you mean it does not work?
Do you get a message box pop-up?
I want you to tell me exactly what it returns each time when you run the code twice.

If you are not getting any Message Box at all, then you probably aren't running the right code!
You should be getting either a Message Box or an error. If you get an error, please tell me what it says, and hit the "Debug" button and tell me which line of code it highlights.