Replies

Thanks Fred. We use several of ELO’s touch screens. Some people have cleaned them with desk cleaner. It works quite well. It cleans well into the membrane holding the 5 wire resistance “stuff.” Destroys it in 3 seconds flat!

My bosses boss wants it for conferencing. I figure the big screen with touch will be in the $15K range. I expect sticker shock will kill the project. In the mean time, thanks for letting me know what to look for.

I am aware that if I change the order in which the workbooks are open, all works fine. However, I cannot ensure the end users follow that same pattern. I have ~90 workbooks in this model. It looks like we will have to put the time into finding all instances where the originator employed this bad programming practice and fix it. Obviously we will need to do some regression testing to ensure the model remains valid. I was hoping someone knew about a fix for this, especially since it worked fine in 4 previous versions of Excel.

Like I often say, I was looking for work applied here, well, I found it!

I don’t want two ranges to have the same name. Someone forgot to tell the accounting folks about this bad practice, therefore that is the way it is. We have a huge model, with speadsheet rolling up 4 layers. Bad programming practice or not, these models have been used for years, until we installed Office 2007.

The files I uploaded are simply a dumbed down model of the problem. By using reductionism, I can isolate the problem and prove it is repeatable.

The actual model uses descriptive range names.

Found it. It is the same as before, but getting there is different.

Now one goes to “Tools, Options” – “Advanced” tab and there is a button at the lower left for file locations. I can keep my custom templates where I wish. If I keep them in a folder below the specified location, they will show under “New” in their own folder. Again, this is the same behavior as before, but finding it is different.

Thanks Tim, but that sets the macro security for one user.

I found it though. I looked in the registry under HKCU to see what changed when I set the macro security, and the applied it to HKLM. It worked. Now whoever logs onto the machine is prompted to run or not run macros.

Sorry ’bout that.

From HKCU to HKLM.

[HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice11.0ExcelSecurity]
“Level”=dword:00000002

Thanks Tim, but that sets the macro security for one user.

I found it though. I looked in the registry under HKCU to see what changed when I set the macro security, and the applied it to HKLM. It worked. Now whoever logs onto the machine is prompted to run or not run macros.

I’d suspect that the problem ones were originally saved with Excel 5/95 compatibility.

I believe you got it right, Rory. Further (but I am not sure yet), It only seems to be those with Excel 5 macros saved in compatibility mode.

Thanks to both of you for the help.

break the zip code down into two cells. The Text to Column feature will do this, but will not preserve the orignial.
The mid formula will work well for you if the zips are consistent.

=MID(B2,1,5) in one column
=MID(B2,7,4) in another column

Sort on the new columns

I figured it out. It is easy. Initially I tried setting SaveAsUI to false, to cancel the save as. I need to trap it instead.

Thanks.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveAsUI = True Then
Cancel = True
End If

End Sub

Thanks. That works, as does autofit, if I don’t have any merged cells. Once the merged cells are in the mix the behavour changes.

Check to see what other events you have associated with the text box. Your code should work.

the exit sub is not needed, the if statement will suffice, but the code works either way.

I tried it by exiting the text control via the tab key and the mouse. Both work.

Use “Exit”. Set Cancel to true to stop the cursor from exiting.

Private Sub tbOne_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If tbOne.Value “1234” Then
tbOne.Value = “”
Cancel = True
End If
End Sub

You have some good advice, but also consider the old fashion way, the command prompt.
The command is

rename myfile.jnk myfile.exe

(assuming you are in the directory where the file is located)

I figured out what I wanted. It is not as fancy as the DB properties window, but it will suffice.

Right click on a shape, select shape, custom properties. In the pop up dialog, select define, then add or delete custom properties at will.

Now go to view, custom properties window. The Custom Properties window can float or be docked. Select a shape to see or edit it’s custom properties.

Custom properties can automaticlly show as text in the shape. In my case, two out of seven custom properties show in the shape. To show the properties, go to insert, field. Select “custom properties” and the field to show. To show a second (or third or fourth …) custom field, select the shape, position the edit cursor after the first field and repeat the steps for inserting a field.

I hope this helps someone else.

Thanks. Now it is clearer to me. I would open a workbook, ignore the security bar, and open the IDE. The security bar would disapear, but when I tried to run a macro the dialog would appear.

Now I can work with this. Thanks again.