Coping command button

[hoyos]

I have copied a command button and paste it in another sheet. I have managed to change the name but the macro won’t work. When right clicking the button I don’t get properties in the drop down box. Is there another way of accessing properties for the command button?

 

[Fluff]

Is it a Form Control Button, or an ActiveX one?

 

[hoyos]

I think it’s a form control button

 

[Fluff]

Then that's why you don't get a properties option in the context menu. What is it that you are trying to do?

 

[hoyos]

I have three buttons in total on a spreadsheet. Pressing button2 to loads the next period, pressing button3 loads the previous period and button1 shows the current period as a number from 0 to 13. But when button1 is copied and pasted on another sheet it’s not indicating what period you are at.

 

[Fluff]

What is the code behind button1?

 

[hoyos]

What is the code behind button1?

the following code:

Sub PeriodBefore()
    P = Range("AB18")
    If P = 13 Then Exit Sub
    Range("D6:F33").Copy
    Cells(432 - (28 * P), 4).PasteSpecial (xlValues)
    Range("I6:K33").Copy
    Cells(432 - (28 * P), 9).PasteSpecial (xlValues)
    Range("O6:O33").Copy
    Cells(432 - (28 * P), 15).PasteSpecial (xlValues)
    Range("V6:Y33").Copy
    Cells(432 - (28 * P), 22).PasteSpecial (xlValues)
    Range("AB18") = P + 1: P = P + 1
    ActiveSheet.DrawingObjects("Button 2").Caption = "<--  Previous Period"
    ActiveSheet.DrawingObjects("Button 3").Caption = "Next 28 Day Period  -->  "
    ActiveSheet.DrawingObjects("Button 1").Caption = "Current Period = " & -P
    If P = 13 Then ActiveSheet.DrawingObjects("Button 1").Caption = "<--  First Period"
    Range(Cells(432 - (28 * P), 4), Cells(459 - (28 * P), 6)).Copy
    Range("D6").PasteSpecial (xlValues)
    Range(Cells(432 - (28 * P), 9), Cells(459 - (28 * P), 11)).Copy
    Range("I6").PasteSpecial (xlValues)
    Range(Cells(432 - (28 * P), 15), Cells(459 - (28 * P), 15)).Copy
    Range("O6").PasteSpecial (xlValues)
    Application.CutCopyMode = False
    Range("D6").Select
End Sub

Sub PeriodAfter()
    P = Range("AB18")
    If P = 0 Then NewPeriod: Exit Sub
    Range("D6:F33").Copy
    Cells(432 - (28 * P), 4).PasteSpecial (xlValues)
    Range("I6:K33").Copy
    Cells(432 - (28 * P), 9).PasteSpecial (xlValues)
    Range("O6:O33").Copy
    Cells(432 - (28 * P), 15).PasteSpecial (xlValues)
    Range("V6:Y33").Copy
    Cells(432 - (28 * P), 22).PasteSpecial (xlValues)
    Range("AB18") = P - 1: P = P - 1
    ActiveSheet.DrawingObjects("Button 2").Caption = "<--  Previous Period"
    ActiveSheet.DrawingObjects("Button 3").Caption = "Next 28 Day Period  -->  "
    ActiveSheet.DrawingObjects("Button 1").Caption = "Current Period = " & -P
    If P = 0 Then ActiveSheet.DrawingObjects("Button 3").Caption = "New 28 Day Period  -->  "
    Range(Cells(432 - (28 * P), 4), Cells(459 - (28 * P), 6)).Copy
    Range("D6").PasteSpecial (xlValues)
    Range(Cells(432 - (28 * P), 9), Cells(459 - (28 * P), 11)).Copy
    Range("I6").PasteSpecial (xlValues)
    Range(Cells(432 - (28 * P), 15), Cells(459 - (28 * P), 15)).Copy
    Range("O6").PasteSpecial (xlValues)
    Application.CutCopyMode = False
    Range("D6").Select
End Sub

 

[Fluff]

Thanks for that, do you want the button you copied to act on the sheet it's on, or the sheet you copied it from?

 

[hoyos]

Paramedic FTL Duty Hours.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Louise Cox				Duty and Flying Hours Record Sheet							From	6 Oct		to 2 Nov 2020
2
3				DUTY HOURS					FLYING HOURS					OFF
4	Day No	Day	Date	Duty Period			28 Day Total	7 Day Total	Flight Times			28 Day Total	7 Day Total	14 Day Total	Duty / Remarks
5				Start	Finish	Hours			First T/O	Last Ldg	Fly Hrs
6	1	Tue	06-Oct	08:00	18:00	10:00	180:00	50:00				0:00	0:00	0	Training
7	2	Wed	07-Oct	08:00	18:00	10:00	180:00	50:00				0:00	0:00	0	Training
8	3	Thu	08-Oct	08:00	18:00	10:00	180:00	50:00				0:00	0:00	0	Training
9	4	Fri	09-Oct	08:00	18:00	10:00	190:00	50:00				0:00	0:00	0	Training
10	5	Sat	10-Oct				190:00	50:00				0:00	0:00	0
11	6	Sun	11-Oct				180:00	50:00				0:00	0:00	0
12	7	Mon	12-Oct	08:00	18:00	10:00	180:00	50:00				0:00	0:00	0	Training
13	8	Tue	13-Oct	08:00	18:00	10:00	180:00	50:00				0:00	0:00	0	Training
14	9	Wed	14-Oct				170:00	40:00				0:00	0:00	0
15	10	Thu	15-Oct	08:00	18:00	10:00	180:00	40:00				0:00	0:00	0	Training
16	11	Fri	16-Oct	08:00	18:00	10:00	180:00	40:00				0:00	0:00	0	Training
Louise Cox

Cell Formulas
Range	Formula
M1	M1	=C6
O1	O1	=" to " & TEXT(C33,"d mmm yyyy")
B6:B16	B6	=VLOOKUP(WEEKDAY(C6),$AA$6:$AB$12,2)
C6	C6	=AB16-(AB18*28)
C7:C16	C7	=C6+1
G6	G6	=SUM(F$6:F6,AD6:AD$33)
H6:H11	H6	=SUM(F$6:F6,AD28:AD$33)
G7:G16	G7	=SUM(F$6:F7,AD8:AD$33)
H12:H16,M12:M16	H12	=SUM(F6:F12)
L6	L6	=SUM(K$6:K6,AE6:AE$33)
M6:M11	M6	=SUM(K$6:K6,AE28:AE$33)
N6:N16	N6	=SUM(Y$6:Y6,AF21:AF$33)
L7:L16	L7	=SUM(K$6:K7,AE8:AE$33)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:N33	Cell Value	<3	text	YES

 

[hoyos]

Thanks for that, do you want the button you copied to act on the sheet it's on, or the sheet you copied it from?

The sheet it's on