PDA

View Full Version : Flags on birthday calendar



perhol
12-17-2019, 08:57 AM
Re-posting a question that never got an answer!


The following VBA code is created for Excel 2003.
It is ment to dele existing flags on Sheet1 and Sheet6 and put new flags in each cell where the text "* år" occurs.


The flag is positioned in the cell's upper right corner.


However, I have switched to Excel 2007, and here the code is not working as expected on Sheet1.
Instead CommandButton7 is placed on Sheet1 in one of the cells where the text "* year" occurs and the flags is positioned at the cell B36.


On Sheet6 the code works as expected!


I can not figure out what is wrong.
Is there anyone who can help?


Here's the code - All comments have been translated from Danish to English:


Sub SætFlag() 'Sub PlaceFlags
'Screen updating off
Application.ScreenUpdating = False
'Flag on sheet2 is copied
Ark2.Shapes("Billede 1").Copy
'The calendar sheets are designated
For Each sht In Array(Ark1, Ark6)
'Calendar sheets are unlocked
With sht
.Unprotect
'Existing flags on calendar sheets are deleted
For Each sh In .Shapes
If sh.Type = msoPicture Then
sh.Delete
End If
'Unlocking and deleting is repeated on the next calendar sheet
Next sh
'New flags are inserted at each birthday on calendar sheets
For Each c In .Range("D3:D33, H3:H33, L3:L33, P3:P33, T3:T33, X3:X33, AB3:AB33, AF3:AF33, AJ3:AJ33, AN3:AN33, AR3:AR33, AV3:Av33")
If c.Value Like "*år.*" Then
Ark2.Shapes("Billede 1").Copy
.Paste
'The location of the flags is determined to
'the top of the cell
'left side of cell + width of cell - width of flag
With .Shapes(.Shapes.Count)
.Top = c.Top
.Left = c.Left + c.Width - .Width
End With
End If
Next c
'Calendar sheets are locked again
.Protect
End With
'New flags are inserted on the next calendar sheet
Next sht
'Screen updating is turned on again
Application.ScreenUpdating = True
End Sub


The file is attached

snb
12-17-2019, 11:21 AM
Why not outlook ?

perhol
12-17-2019, 01:19 PM
I don't use Outlook privately.
And I like Excel :-)

p45cal
12-17-2019, 07:10 PM
A guess, cell A1 on sheet Ar has the formula =PROPER(TEXT(…~~ which means the result is a string.
Many cells (eg. Fødselsdage'!$BU$4) are dependent on this cell being a date/number ( YEAR(År!$A$1)).
Have you changed the formula in cell A1 of sheet Ar?

I haven't looked any deeper.

perhol
12-18-2019, 01:35 AM
> p45cal
I have tried to change the format to 'date' as you suggest.
This does not change anything, and cell BU4 on the 'Birthdays' sheet would also show the number of years a person filled that year if cells BR4 and BS4 on the 'Birthdays' sheet were filled.
The problem is apparently not a format in any cell, but that CommandButton7 is placed on Sheet1 in one of the cells where the text "* year" occurs and the flags is positioned at the cell B36.


On sheet6 'Year (4 x Quarter)' the flags are placed correctly!

p45cal
12-18-2019, 04:55 AM
1. When I run SætFlag I get:
25650
I have no idea what's in that white space. It happens twice. when I delete that white space all is OK.

2. When first opening your file I get this:
25651

when I change the formula in A1 of Ar from:
=PROPER(TEXT(AY$9,"mmmm åååå"))
to:
=PROPER(TEXT(AY$9,"dd/mm/yyyy"))
I get this:
25652

Then when I run SætFlag I get:
25653on both sheets.

That formula change in A1 is clearly needed here in the UK due to locale differences.

snb
12-18-2019, 05:47 AM
You might use VBA:


Sub SætFlag()
Ark2.Shapes("Billede 1").Copy
For Each it In Array(Ark1, Ark6)
it.Shapes.SelectAll
Selection.Delete
it.Paste it.Range("A3:AV33").SpecialCells(-4123, 16)
Next
End Sub

perhol
12-19-2019, 05:35 PM
> p45cal
I do not experience the same as you whether I perform the changes you propose or not.


> snb
If I use your suggestion I get another error message and can not move on.

-----------------------------------------------------------------------------------------------
I found another version of the file with a different VBA code.
When this code is executed, an error message appears
"Run-time error '1004':
Paste method of Worksheet class failed. "
I press the 'Debug' button.
In the VBA Editor, the line
ActiveSheet.Paste is highlighted, but I can continue to run the code by pressing "Continue (F5)" in the toolbar.
All flags are then placed correctly.
How do I avoid this error message?
I'm attaching this version of the file!
25667

snb
12-20-2019, 02:12 AM
If I use your suggestion I get another error message and can not move on.

If I read your reaction I see it doesn't contain any relevant information.

perhol
12-21-2019, 04:25 AM
I tried the file at work. There we use Office 2010.
The file ran flawlessly!
I then tried again at home in Excel 2007 with the same errors as previously described.
A colleague wanted a copy of the calendar, so I deleted my family's birthdays and inserted a few so he could see exactly how a birthday should be typed into the sheet.
Now the file ran flawlessly on Excel 2007!
I then opened the original file with my family's birthdays and removed 1 birthday at a time and tested the file again.
When there are 16 birthdays it works flawlessly!
When there are 17 or more birthdays it fails again!

How can that be?

paulked
12-21-2019, 05:01 AM
This is a 'blind' question, but is the error tied to a specific date/day/month?

perhol
12-21-2019, 06:09 AM
Tried to remove all birthdays and put test birthdays across all 12 months, 'Test 1', 'Test 2', etc.
It worked until 'Test 15'. At 'Test 16' the code failed again.
So no, the error is not tied to a specific date / day / month?