Change event to add button
 

Hi,
I am trying to create a worksheet change event where when a user pastes a group of cells onto a page a print button appears on the page. The problem is that the code runs twice (the second when it hits Cells(1,1).Select) and I get an out of memory error on Target.Value. The code is poted below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells(1, 1).Select
If Target.Address = "$A$1" And Target.Value < "" And x = 0 Then
Buttons.Delete
Buttons.Add(624, 15, 48, 15).Select
With Selection
.Caption = "Print"
.OnAction = "Print_Tickets"
End With
x = x + 1
Cells(1, 1).Select
End If
End Sub

So, the question is, how do I get the emphasis taken off of the button, so I don't have to use Cells(1, 1).Select to do it?
Thanks.
James

Change event to add button
 

On Wednesday, March 6, 2013 1:27:25 PM UTC-8, wrote:
Hi,

I am trying to create a worksheet change event where when a user pastes a group of cells onto a page a print button appears on the page. The problem is that the code runs twice (the second when it hits Cells(1,1).Select) and I get an out of memory error on Target.Value. The code is poted below.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells(1, 1).Select

If Target.Address = "$A$1" And Target.Value < "" And x = 0 Then

Buttons.Delete

Buttons.Add(624, 15, 48, 15).Select

With Selection

.Caption = "Print"

.OnAction = "Print_Tickets"

End With

x = x + 1

Cells(1, 1).Select

End If

End Sub



So, the question is, how do I get the emphasis taken off of the button, so I don't have to use Cells(1, 1).Select to do it?

Thanks.

James

Not sure if this will do what you want. I assigned the code to keystroke
ctrl + p.

Option Explicit
Sub P_Button()
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x
Buttons.Delete
Buttons.Add(624, 15, 48, 15).Select
With Selection
.Caption = "Print"
.OnAction = "Print_Tickets"
End With
x = x + 1
Application.CutCopyMode = False
Application.EnableEvents = False
'Cells(1, 1).Select
Application.EnableEvents = True
Cells(1, 1).Select
End Sub

Regards,
Howard

Change event to add button
 

On Wednesday, March 6, 2013 4:08:50 PM UTC-6, Howard wrote:
On Wednesday, March 6, 2013 1:27:25 PM UTC-8, wrote:

Hi,



I am trying to create a worksheet change event where when a user pastes a group of cells onto a page a print button appears on the page. The problem is that the code runs twice (the second when it hits Cells(1,1).Select) and I get an out of memory error on Target.Value. The code is poted below.







Private Sub Worksheet_SelectionChange(ByVal Target As Range)



Cells(1, 1).Select



If Target.Address = "$A$1" And Target.Value < "" And x = 0 Then



Buttons.Delete



Buttons.Add(624, 15, 48, 15).Select



With Selection



.Caption = "Print"



.OnAction = "Print_Tickets"



End With



x = x + 1



Cells(1, 1).Select



End If



End Sub







So, the question is, how do I get the emphasis taken off of the button, so I don't have to use Cells(1, 1).Select to do it?



Thanks.



James



Not sure if this will do what you want. I assigned the code to keystroke

ctrl + p.



Option Explicit

Sub P_Button()

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim x

Buttons.Delete

Buttons.Add(624, 15, 48, 15).Select

With Selection

.Caption = "Print"

.OnAction = "Print_Tickets"

End With

x = x + 1

Application.CutCopyMode = False

Application.EnableEvents = False

'Cells(1, 1).Select

Application.EnableEvents = True

Cells(1, 1).Select

End Sub



Regards,

Howard

Howard,
You are a genius! It worked like a charm and I thank you for your help!
James