repetitive macros-solved

I have a work book with several (couple of hundred) buttons, which when clicked display the contents of a range of cells in a message box as shown below;

Sub v103_Click()
 
MsgBox Join(Application.Transpose(Application.Transpose(Range("Sheet1!A103:Sheet1!J103"))), Chr(10))
 
End Sub

Sub v104_Click()
 
MsgBox Join(Application.Transpose(Application.Transpose(Range("Sheet1!A104:Sheet1!J104"))), Chr(10))
 
End Sub

Sub v105_Click()
 
MsgBox Join(Application.Transpose(Application.Transpose(Range("Sheet1!A105:Sheet1!J105"))), Chr(10))
 
End Sub

Does anyone know of a more efficent way of coding this rather than copy,paste,edit,copy,paste,edit,copy,paste,edit........ad infinitum.

use excel itself
in a blank sheet
in a1 put

="Sub v"&CEILING(ROWS($A$1:A307)/3,1)&"_Click()"

in a2

="MsgBox Join(Application.Transpose(Application.Transpose(Range(""Sheet1!A"&CEILING(ROWS($A$1:A307)/3,1)&":Sheet1!J"&CEILING(ROWS($A$1:A307)/3,1)&"""))),chr(10))"

in a3

End Sub

then select a1:a3 and drag down as many as you need
copy paste the lot into your vba
then again there might be a vba way that is far cleverer but beyond me lol

henrysmith,

Add a new button, and put the following code in it:

Sub NewButton_Click()

Dim WhatRow As Variant
WhatRow = InputBox("What row number do you want to display?")
If IsNumeric(WhatRow) Then
  MsgBox Join(Application.Transpose(Application.Transpose(Range("Sheet1!A" & WhatRow & ":Sheet1!J" & WhatRow & ""))), Chr(10))
Else
  MsgBox "You did not enter a valid integer number - macro terminated!"
End If

End Sub

If you are using the buttons from the Forms toolbar, you could label the buttons with the row number, and assign all the buttons to this Macro.

rw = ActiveSheet.Buttons(Application.Caller).Caption

MsgBox Join(Application.Transpose(Application.Transpose(Range("Sheet1!A" & rw & ":Sheet1!J" & rw & ""))), Chr(10))