Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
apply cell change event to single column - WorksheetChange Event | Excel Programming | |||
Worksheet change event - Spinner button | Excel Programming | |||
How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
change event/after update event?? | Excel Programming |