Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste upon click
I would like to write a macro, to be attached to a label, that upon clicking
the label, would put the named range on the clipboard, and then paste the range at a cell in the worksheet with another click. current code: Sub More_time() Range("More_Time").Select Selection.Copy End Sub would like to paste by clicking |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste upon click
This assumes the label is from the Control Toolbox and is on the same sheet
as the named cell, and that the receiving cell is on the same page. Copy both subs to the sheet code module. Click the label, then click a cell. Don't forget to save the file to keep the code. You can name the label and update the code, in case you have more than one label. Private Sub Label1_Click() Range("More_Time").Copy End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.PasteSpecial xlPasteValues End Sub "bb pcv" <bb wrote in message ... I would like to write a macro, to be attached to a label, that upon clicking the label, would put the named range on the clipboard, and then paste the range at a cell in the worksheet with another click. current code: Sub More_time() Range("More_Time").Select Selection.Copy End Sub would like to paste by clicking |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste upon click
I could not get this code to work.
Maybe a fuller explaination of what I'm trying to do will help. I have 10 employees who see clients throughout the day. Appointment lengths are predicated on the number of programs clients are requesting. The worksheet has the employees in Column A and the times in Columns B:AS. Each cell in the appointment grid represents 15 minutes. At the top of the grid, I've named ranges (a, b, c, etc) that represent different lengths of appointments, and color coded the ranges so that the different appointment types are readily distinguishable when pasted into the main grid. I have placed a Forms Label over each of the appointment types and attached a macro that slects the range and places it on the clipboard (Range("A").select Selection.Copy) The user (Receptionist) clicks on the appropriate appointment type - launching the macro - and then places the cursor in the intersection of time and employee. To paste the selection, they must use the keyboard shortcut or right click. I'd like to have them select the range with one click and then paste with another. Thanks for any help you can give... "JLGWhiz" wrote: This assumes the label is from the Control Toolbox and is on the same sheet as the named cell, and that the receiving cell is on the same page. Copy both subs to the sheet code module. Click the label, then click a cell. Don't forget to save the file to keep the code. You can name the label and update the code, in case you have more than one label. Private Sub Label1_Click() Range("More_Time").Copy End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.PasteSpecial xlPasteValues End Sub "bb pcv" <bb wrote in message ... I would like to write a macro, to be attached to a label, that upon clicking the label, would put the named range on the clipboard, and then paste the range at a cell in the worksheet with another click. current code: Sub More_time() Range("More_Time").Select Selection.Copy End Sub would like to paste by clicking |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste upon click
As I read your description of the current set up, it seems that all you need
is the code to paste the clipboard contents on the sheet. The code below will do thatl Right click the sheet tab and click view code on the pop up menu. Then paste this snippet into the code window. So long as any cell other than the currently selected cell is clicked, the paste action will occur, so be sure to click the correct cell the first time. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub If you want formatting and all to be pasted, just change the Paste:=xlPasteValues to Paste:=xlPasteAll "bb pcv" wrote in message ... I could not get this code to work. Maybe a fuller explaination of what I'm trying to do will help. I have 10 employees who see clients throughout the day. Appointment lengths are predicated on the number of programs clients are requesting. The worksheet has the employees in Column A and the times in Columns B:AS. Each cell in the appointment grid represents 15 minutes. At the top of the grid, I've named ranges (a, b, c, etc) that represent different lengths of appointments, and color coded the ranges so that the different appointment types are readily distinguishable when pasted into the main grid. I have placed a Forms Label over each of the appointment types and attached a macro that slects the range and places it on the clipboard (Range("A").select Selection.Copy) The user (Receptionist) clicks on the appropriate appointment type - launching the macro - and then places the cursor in the intersection of time and employee. To paste the selection, they must use the keyboard shortcut or right click. I'd like to have them select the range with one click and then paste with another. Thanks for any help you can give... "JLGWhiz" wrote: This assumes the label is from the Control Toolbox and is on the same sheet as the named cell, and that the receiving cell is on the same page. Copy both subs to the sheet code module. Click the label, then click a cell. Don't forget to save the file to keep the code. You can name the label and update the code, in case you have more than one label. Private Sub Label1_Click() Range("More_Time").Copy End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.PasteSpecial xlPasteValues End Sub "bb pcv" <bb wrote in message ... I would like to write a macro, to be attached to a label, that upon clicking the label, would put the named range on the clipboard, and then paste the range at a cell in the worksheet with another click. current code: Sub More_time() Range("More_Time").Select Selection.Copy End Sub would like to paste by clicking |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste upon click
Additional note to pasting from the clipboard. The paste special method
will produce an error message that "PasteSpecial method failed" if there is nothing on the clipboard when the code runs. Therefore, the user should be sure that they do nothing between the copy action and the paste special action that would clear the clipboard. "bb pcv" wrote in message ... I could not get this code to work. Maybe a fuller explaination of what I'm trying to do will help. I have 10 employees who see clients throughout the day. Appointment lengths are predicated on the number of programs clients are requesting. The worksheet has the employees in Column A and the times in Columns B:AS. Each cell in the appointment grid represents 15 minutes. At the top of the grid, I've named ranges (a, b, c, etc) that represent different lengths of appointments, and color coded the ranges so that the different appointment types are readily distinguishable when pasted into the main grid. I have placed a Forms Label over each of the appointment types and attached a macro that slects the range and places it on the clipboard (Range("A").select Selection.Copy) The user (Receptionist) clicks on the appropriate appointment type - launching the macro - and then places the cursor in the intersection of time and employee. To paste the selection, they must use the keyboard shortcut or right click. I'd like to have them select the range with one click and then paste with another. Thanks for any help you can give... "JLGWhiz" wrote: This assumes the label is from the Control Toolbox and is on the same sheet as the named cell, and that the receiving cell is on the same page. Copy both subs to the sheet code module. Click the label, then click a cell. Don't forget to save the file to keep the code. You can name the label and update the code, in case you have more than one label. Private Sub Label1_Click() Range("More_Time").Copy End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.PasteSpecial xlPasteValues End Sub "bb pcv" <bb wrote in message ... I would like to write a macro, to be attached to a label, that upon clicking the label, would put the named range on the clipboard, and then paste the range at a cell in the worksheet with another click. current code: Sub More_time() Range("More_Time").Select Selection.Copy End Sub would like to paste by clicking |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Macro - I think... Copy - Paste on Click | Excel Discussion (Misc queries) | |||
UserForm - Right-click a Textbox and Paste | Excel Programming | |||
why can't you right click then paste inside screentip box? | Excel Discussion (Misc queries) | |||
The right click on my mouse does not give me the option to paste | Excel Discussion (Misc queries) | |||
Macro for one-click paste special transpose | Excel Programming |