Enamel Station Sign Kit Tutorial Part 1

[Sly401]

Hi Guys

I have put together a kit to make enamel station signs a bit easier.. but think you are gonna need a tutorial thanks to my wierd way of thinking so here goes.




Outline of technique

We use an excel spreadsheet template to produce a texture on your clipboard.
This is then pasted into your Paint program and saved.
Convert to ACE format a place in the required "Source" location
Open the relevant Blueprint in the Editor, Edit a template blueprint and export
View and place in game

Open The Supplied Excel Spreadsheet

Font should be Gil Sans MT , however not everybodys excel will have this.. use MS Sans Seriff



The Top row has a set of Icon buttons to select which Region / Colour you require



Choose the Sign type you wish to make and edit the text in the Black and Orange Boxes





It should Preview on screen





Click the relevant Button to Copy to Your Clipboard




Thats it for the excel bit... cya in part two

Sly

[npcleary]

Sly, This will be a lifesaver but I am having problems with the Excel spreadsheet! I'm running Excel 2007 and have opened the file, which Excel opens in compatability mode as it is a .xls, and then enabled macros. On clicking on the "Western Region" command button I'm getting a "Run-time Error 1004". Launching debug shows the code stalling on the first line "Selection.ShapeRange.Fill.Visible = msoTrue". I have tried saving the file as an Excel 2007 macro enabled spreadsheet (.xlsm) but am hitting the same problem.

I'm assuming it is a macro compatability issue with Excel 2007? Any ideas?

Nick

[Sly401]

Hi Nick

I was worried about compatability
However nothing is locked out so we should be able to sort it..

The macro's for the buttons fill the backgrounds with the req colors..
The western region one I think is the only one that uses a Custom colour all the others use system palettte... I think

If the other buttons work ok then I suggest you view the code
there will be two instances of fill colour in each fill macro ending in RGB.XXX,XXX,XXX if its custom and ..........system 18 or similar if its system palette

Do the others work OK ?

You can always paste in the code from one of the others then edit the color references..

Edit

Here is the code for western region button

Private Sub WESTERNREGION_Click()

ActiveSheet.Shapes("Group 480").Select
ActiveSheet.Shapes.Range(Array("Group 480", "Group 520")).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(116, 81, 60)
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 3#
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 26
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
ActiveSheet.Shapes("Group 482").Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485" _
, "Group 486")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485" _
, "Group 486", "Group 487")).Select

ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485" _
, "Group 486", "Group 487", "Group 488")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485" _
, "Group 486", "Group 487", "Group 488", "Group 489")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485" _
, "Group 486", "Group 487", "Group 488", "Group 489" _
, "Group 490")).Select
ActiveSheet.Shapes.Range(Array("Group 482", "Group 483", "Group 484", _
"Group 485" _
, "Group 486", "Group 487", "Group 488", "Group 489" _
, "Group 490", "Group 493")).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(116, 81, 60)
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 5#
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse

The Bold entries are the colour of the background
Sly

[npcleary]

Hi Sly

Something fairly fundamental going on here! Same error codes with all the buttons - all the regions and the various "copy to clipboard". I knew I shouldn't have upgraded to Office 2007. I'm OK with earlier versions but this one has me baffled - I'll play around for a while and get back to you....

Nick

[npcleary]

Sly, No luck with this and I have taken the cowards way out and moved to a multiple Office installation (needed to do this for work sometime anyway ). Works fine in 2000 and 2003. I am convinced that it is a security issue as 2007 is very strict with how it handles macro security. I have tried setting security to the minimum, making the folder the ssheet resides in "trusted" and various other tweaks, all to no avail - My partner contracts as a technical author for Microsoft and even she is struggling!

Sorry to give up on this but if anyone else has got it working in Office 2007 let me know how.....Nick

[Sly401]

Ok no worries Nick

Thanks for trying and its good to know it works in 2000 and 2003 (it was made on 03 )

I don't have 2007 (phew) so I can't test

I agree if none of the macro's work it must be a security thing

Hope it does what you want OK.. Feel free to edit anything at all, some fonts get stretched so you may want to improve that (strips, doorplate and single row are the worst )

Have Fun

Sly

As an afterthought
If you want to record your own macro's for 2007 the original region selections are made to

select the Totem preview group and the Totem artwork group (way off to the right)
right click and format.... Colors and lines
pick the fill colour, pick the line colour... (I think width is 5 ?)
and OK it.
select all the other preview and art work groups
right click format Colors and lines
pick the fill color
select no line
and Ok it..

(It is two operations because the totems have line borders and the other signs do not)

The copy macros select the artwork (way right ) relevant to the button and copy to clipboard.

Sly

[npcleary]

Thanks Sly - when I have regained the will to live I'll have a go at the 2007 version... Incidentally, Sheet 3 is very interesting - is it there for a purpose or just to remind everyone of good times passed? I notice that "my" railways in and around the Forest of Dean don't have anything listed.... Nick

[Sly401]

Oooh for a minute you had me worried what I had left in there

The list of closed railway stations... yep it is interesting. it was left over from a start when I just had totems... I was going to use the list to produce a totem for every station closed after 1960 (before then they might not have been fitted)

Then I found out how many there was... hence the kit to do it yourself

Link for the tables
http://en.wikipedia.org/wiki/List_of_cl ... itain:_D-F

let me know if you find anything on the 2007 i may be able to incorporate it..

thanks

Sly

[npcleary]

Not as easy as I first thought! Uninstalled Office 2007 and then installed Office 2000, 2003 and 2007 in that order....Excel working fine in all 3 versions with a number of spreadsheets. However, Totems and Signs.xls is doing strange things in 2007 - enabled all macros and the regional colour buttons are still not working but the copy to clipboard buttons do work after a fashion. Only problem is that they only copy the coloured rectangle or totem and not the relevant text boxes - appears that the grouping is screwed... Has anyone out there got this working under 2007 and is it just me going crazy or have Microsoft done it again...???

[npcleary]

Sly

There does appear to be an Excel 2007 issue that is affecting this. Under 2003 you have used a combination of drawing shapes and control text boxes which are then assembled in their various groups - no problem doing this with 200, 2003 etc, standard practice. Under 2007, and I quote the help file, " You cannot combine Form controls or ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection." hence none of your grouping is working in 2007 and this appears to be screwing up the macros. I have tested this on a blank 2007 spreadsheet by mimicking your control text boxes and shapes and they just won't group whereas there is no problem in 2003!

Unless someone can come up with a better idea I am going to look at substituting the control text boxes with Drawing text boxes (can still be linked).... I notice that there have been a good few downloads of the tool but no other comments re 2007 - is it me? Nick

[Sly401]

Hi Nick

I have had no PM's so I presume that the only prob is with 2007

The quote refering to objects linked will definatley be the cause ........ Why MS ?

As an alternative you could...

Ungroup everything record a macro selecting all the objects except the totems one by one
then applying fill colour.
then selecting no line
Unselect..select the totem background components ( 4 off)
Filll background color
Fill line colour

make a note of the original colours then paste the script into each button, and correct the colour shades.

I could write it that way with 2003 and will make V2 using the above method.. groups were easier and simple lol..now just need a bit of time

Sly

[davesurf]

Hi Nic

I have also attempted to run the program on Excell 2007 and I too have the same problem as you.

I checked the microsoft pages and you are able to download a conversion tool for Excell 2003 that will allow you to save your files in Excell 2003 as 2007 files. Having downloaded this tool and then saving Slys spreadsheet as 2007 version I a tried to open it in Excell 2007 but the problem is still there.

Dave

[Sly401]

Ok guys
I will write new macro's this weekend that will eliminate the error caused by grouping, that way it will hopefully be universal

It will prolly be sunday before I get round to it and will place a version 2 on the site........ I will also paste copies of the macro's here in case you just wany to paste in

apologies I wasn't aware that MS had made groups incompatable in 2007..

Sly

[npcleary]

Sly - according to the feedback my partner has got from MS the change was done because of security and indexing (??) Thanks for the offer of v2 - I was just about to start on it! If you want me to test under 2007 just PM me... thanks again, Nick

[Sly401]

Thanks Nick

I will need someone to test in 07 .... 03 is my newest version..

I can't see a problem its just gonna be a much longer macro selecting individual items

be in touch over the weekend or Pm me you email so I have it ready

Sly

Just as an aside to anyone out there... the textures that you make and export can be substituted fot the default station signs if you so wish.
simply for eg make a Bath Green Park in maroon and export.
Go into assets and Copy your Enamel _nameboard_bath gren park TgPcDx
look in Kuju assets and replace Station Sign Bath Green park TgPcDx renaming to match the original