ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   add vbyesno to existing macro (https://www.excelbanter.com/excel-programming/416695-add-vbyesno-existing-macro.html)

SteveDB1
add vbyesno to existing macro
 
Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")



Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy


ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.


Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

Anand.V.V.N
add vbyesno to existing macro
 
if vbyes then
' here you can use call or use the name of the macro
claimN
else
endif

Try this, it should work.
Anand.V.V.N

--
"Who will guard the guards?"


"SteveDB1" wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")



Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy


ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.


Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

Dave Peterson
add vbyesno to existing macro
 
So you're copying a value and pasting it 4 rows down as many times as the user
wants. And doing some formatting to that pasted cell.

I think that this does what you want:

Option Explicit
Sub ClaimN()

Dim Nu As Long
Dim i As Long
Dim Resp As Long
Dim RngToCopy As Range
Dim DestCell As Range
Dim HowManyRowsBelow As Long

HowManyRowsBelow = 4

Nu = CLng(Application.InputBox _
(prompt:="How many rows is your Abstract Sheet?", _
Title:="Number of Rows"))

If Nu < 1 Then
Exit Sub
End If

'some kind of typing check???
If Nu 50 Then
MsgBox "Too many rows down"
Exit Sub
End If

Set RngToCopy = ActiveCell 'just a single cell

Application.ScreenUpdating = False

Do
For i = 1 To Nu
Set DestCell = RngToCopy.Offset(i * HowManyRowsBelow, 0)

RngToCopy.Copy _
Destination:=DestCell

With DestCell
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
Next i

Resp = MsgBox(prompt:="do you wish to select next column?", _
Buttons:=vbYesNo, Title:="Select Next Column")
If Resp = vbYes Then
Set RngToCopy = RngToCopy.Offset(0, 1)
Else
Exit Do
End If

Loop

'I'd do some testing before using this line!
'ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub


SteveDB1 wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")

Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy

ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.

Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

--

Dave Peterson

SteveDB1
add vbyesno to existing macro
 
hi Dave,
I have tried it just before lunch, and found a bug I need to resolve. I
remember having this bug before, and got it fixed.
I need to reboot, and once I get this resolved, or if I can't, I'll be back
and let you know the results.
Best, and thank you.
SteveB

"Dave Peterson" wrote:

So you're copying a value and pasting it 4 rows down as many times as the user
wants. And doing some formatting to that pasted cell.

I think that this does what you want:

Option Explicit
Sub ClaimN()

Dim Nu As Long
Dim i As Long
Dim Resp As Long
Dim RngToCopy As Range
Dim DestCell As Range
Dim HowManyRowsBelow As Long

HowManyRowsBelow = 4

Nu = CLng(Application.InputBox _
(prompt:="How many rows is your Abstract Sheet?", _
Title:="Number of Rows"))

If Nu < 1 Then
Exit Sub
End If

'some kind of typing check???
If Nu 50 Then
MsgBox "Too many rows down"
Exit Sub
End If

Set RngToCopy = ActiveCell 'just a single cell

Application.ScreenUpdating = False

Do
For i = 1 To Nu
Set DestCell = RngToCopy.Offset(i * HowManyRowsBelow, 0)

RngToCopy.Copy _
Destination:=DestCell

With DestCell
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
Next i

Resp = MsgBox(prompt:="do you wish to select next column?", _
Buttons:=vbYesNo, Title:="Select Next Column")
If Resp = vbYes Then
Set RngToCopy = RngToCopy.Offset(0, 1)
Else
Exit Do
End If

Loop

'I'd do some testing before using this line!
'ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub


SteveDB1 wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")

Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy

ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.

Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

--

Dave Peterson

SteveDB1
add vbyesno to existing macro
 
Anand, I did try this iand it threw an error, I'm thinking because my macro
is within my xlam file which requires a Control as IRibboncontrol.
Thanks though.


"Anand.V.V.N" wrote:

if vbyes then
' here you can use call or use the name of the macro
claimN
else
endif

Try this, it should work.
Anand.V.V.N

--
"Who will guard the guards?"


"SteveDB1" wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")



Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy


ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.


Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

SteveDB1
add vbyesno to existing macro
 
Hi again.
Ok, back from reboot world.
Yes, copying, and pasting four (merged in to a single) rows down for as long
as the user chooses. I'm then ensuring the formatting to the pasted cell
remains as previously formatted.
After a couple of try's I realized what the issue was.
I'm now in the black-- it works, and thank you.
I used a couple of your elements to make mine do what I want. While it's not
as sophisticated as yours, it does work.
The do loop, the exit do, and the offset to bring it back to the top, to
offset and repeat the process.
Thank you for your help.
Best.
SteveB



"Dave Peterson" wrote:

So you're copying a value and pasting it 4 rows down as many times as the user
wants. And doing some formatting to that pasted cell.

I think that this does what you want:

Option Explicit
Sub ClaimN()

Dim Nu As Long
Dim i As Long
Dim Resp As Long
Dim RngToCopy As Range
Dim DestCell As Range
Dim HowManyRowsBelow As Long

HowManyRowsBelow = 4

Nu = CLng(Application.InputBox _
(prompt:="How many rows is your Abstract Sheet?", _
Title:="Number of Rows"))

If Nu < 1 Then
Exit Sub
End If

'some kind of typing check???
If Nu 50 Then
MsgBox "Too many rows down"
Exit Sub
End If

Set RngToCopy = ActiveCell 'just a single cell

Application.ScreenUpdating = False

Do
For i = 1 To Nu
Set DestCell = RngToCopy.Offset(i * HowManyRowsBelow, 0)

RngToCopy.Copy _
Destination:=DestCell

With DestCell
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
Next i

Resp = MsgBox(prompt:="do you wish to select next column?", _
Buttons:=vbYesNo, Title:="Select Next Column")
If Resp = vbYes Then
Set RngToCopy = RngToCopy.Offset(0, 1)
Else
Exit Do
End If

Loop

'I'd do some testing before using this line!
'ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub


SteveDB1 wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")

Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy

ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.

Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

--

Dave Peterson

Dave Peterson
add vbyesno to existing macro
 
If the problem is in using the code in a ribbon, you may want to start a new
thread and change the subject line accordingly.



SteveDB1 wrote:

hi Dave,
I have tried it just before lunch, and found a bug I need to resolve. I
remember having this bug before, and got it fixed.
I need to reboot, and once I get this resolved, or if I can't, I'll be back
and let you know the results.
Best, and thank you.
SteveB

"Dave Peterson" wrote:

So you're copying a value and pasting it 4 rows down as many times as the user
wants. And doing some formatting to that pasted cell.

I think that this does what you want:

Option Explicit
Sub ClaimN()

Dim Nu As Long
Dim i As Long
Dim Resp As Long
Dim RngToCopy As Range
Dim DestCell As Range
Dim HowManyRowsBelow As Long

HowManyRowsBelow = 4

Nu = CLng(Application.InputBox _
(prompt:="How many rows is your Abstract Sheet?", _
Title:="Number of Rows"))

If Nu < 1 Then
Exit Sub
End If

'some kind of typing check???
If Nu 50 Then
MsgBox "Too many rows down"
Exit Sub
End If

Set RngToCopy = ActiveCell 'just a single cell

Application.ScreenUpdating = False

Do
For i = 1 To Nu
Set DestCell = RngToCopy.Offset(i * HowManyRowsBelow, 0)

RngToCopy.Copy _
Destination:=DestCell

With DestCell
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
Next i

Resp = MsgBox(prompt:="do you wish to select next column?", _
Buttons:=vbYesNo, Title:="Select Next Column")
If Resp = vbYes Then
Set RngToCopy = RngToCopy.Offset(0, 1)
Else
Exit Do
End If

Loop

'I'd do some testing before using this line!
'ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub


SteveDB1 wrote:

Morning all.
I have a macro that I want to add a continuation element to.
Presently the macro copies a cell's contents down to the end of my choosing
with an input box. I'd like to add a message box input to continue the
operation into multiple columns.
How do I call the macro to continue?
My code for the macro:
--------------------------------------------------------
Sub ClaimN() '(control As IRibbonControl) have commented out iribbon control
' for testing until it works with addition of continuation input.
'select cells with date, or claim number, and copy to
'next row used for that data.
Dim Nu As Integer
Nu = InputBox(prompt:="How many rows is your Abstract Sheet?",
Title:="Number of Rows")

Application.ScreenUpdating = False
For i = 1 To Nu

Selection.Copy

ActiveCell.Offset(rowoffset:=4, columnoffset:=0).Select

ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
.Font.Bold = False

End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Next i

'here starts my input message box to select next column

Dim msg 'used example out of VB help file.

msg = MsgBox("do you wish to select next column?", vbYesNo, "Select Next_
Column")
If msg = vbYes Then

'what do I place here to start the macro over again?
'I want to select one column over, and have it repeat the same as
'already performed.
'I then want to repeat this as many times as I choose.

Else
msg = vbNo
End Sub

End If

ActiveWorkbook.Save

End Sub
--------------------------------------------------

Thank you for your helps.
Best.
SteveB

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 07:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com