Replies

The link that I posted works for me…

Strange, It is still not working for me, neither with Firefox 3.5.5 nor with IE 8. I have checked with some other links & all are working properly. Is something wrong with installation of browser at my end??

Here is a alternative site to download the same.

An additional requirement :

Is it possible to modify the code to protect workbook structure too but allowing users to move/copy sheets?

Thanks in advance for any help/suggestion.

Laurent Longre has written a free add-in Morefunc for Excel that provides a function INDIRECT.EXT that works like INDIRECT but allows the other workbook to be closed. All users would need to install this add-in.

The link associated to download Add-in has been broken & occurring following error :

Warning: main(./common/init.php) [function.main]: failed to open stream: No such file or directory in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

Fatal error: main() [function.require]: Failed opening required ‘./common/init.php’ (include_path=’/mnt/108/sdc/2/4/xcell05/include:.:/usr/php4/lib/php’) in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

Applying formula provided by Hans, I am getting a #value! error down E11 if C8 is blank. To avoid this, you can use

=IF($C$8=””,””,($C$8+A12))

in E12 and fill it down.

You can use same method for Adjusted Timeline Date Column.

Hi Jezza

That sounds great. Thank you

I’ve attached the spreadsheet and basically when I populate vehicle 1 in January with a figure I want it to add the ‘service only’ figure to the figure I input….are you able to help me with that?

Thanks ever so much – they will think I’m clever!

Penny

What exactly you are trying to achieve? do u want to simply add the cost or want it to auto populate depends on vehicle/month??

One possible reason is “Read only” status of external drive, to/from where you have copied the data. Pl check the property of external drive. Is it marked as “Read Only”? If so, uncheck the read only option and save files again.

I have a standard daily worksheet where I enter the various customers names and some information about the orders. I only have about 20 customers but they have long names. I would like to grab the customer-name from some list or look up in a list. Really much rather autocomplete.

How do I do any of this?

Thanks.
Michael

One simple approach is to use data validation OR vlookup, if you are having a list of names. Pl have a look.

You should use

=ISBLANK(A1)

where A1 is the active cell within the selection (usually the upper left corner). Excel will automatically adjust the cell reference for the other cells within the selection. Do *not* use

=ISBLANK(A1:O26)

where A1:O26 is the selected range!

Another way with similar approach :

Test your techno-jargon! – CNN.com

8 out of 10. Least but not bad.

Sorry, my mistake. The line

If Not strPassword = “psd” Then

should be

If Not strPwd = “psd” Then

If you had had Option Explicit at the top of the module, the Visual Basic Editor would have pointed out the inconsistency.

Thanks Hans, it is perfect now.

If I supplied correct password on second attempt, it fails to unprotect the sheets.

Do you need the user to supply the password to unprotect the sheets, or would it be OK to include the password in the code (like you do in the code that protects the sheets)?

Yes, users are required to supply the password only once to unprotect all protected sheets.

Are all sheets protected?

No Hans, only sheets in array are protected using following code.
[codebox]ub Hide()
Dim strName As Variant
For Each strName In Array(“BG3”, “SSC”, “MUFFLER”, “FRAME”, “RIM”, “HRD”, “PNG”, “ANS”)
With Worksheets(strName)
.Columns(“A:AR”).Hidden = True
.Columns(“AU:AU”).Hidden = True
.Protect Password:=”psd”, AllowFormattingColumns:=True, AllowFormattingRows:=True
.Select
.Range(“AS1”).Select
End With
Next
Sheets(“Key Ratio “).Select
Range(“A3”).Select
End Sub[/codebox]

Do all protected sheets have the same password?

Yes, all protected sheets have the same password.

Thanks Hans but as I stated, it is *NOT* necessarily required to hide particular columns & the code is perfect without any further modification. I was looking for the possibility of avoiding any mis-presentation but that can be taken care of without amending the code. (After all, the users are equally responsible for any (mis) presentation of data.)

Thanks again.

Pl have a look :

[codebox]Sub unhide()
For Each sht In ActiveWorkbook.Sheets
On Error Resume Next
sht.Unprotect
If Err Then
MsgBox “Password incorrect. Please try again.”, vbExclamation
sht.Unprotect
If Err Then
MsgBox “Sorry! better luck next time”, vbExclamation
End If
End If
On Error GoTo 0
Next sht
Dim strName As Variant
For Each strName In Array(“BG3”, “SSC”, “MUFFLER”, “FRAME”, “RIM”, “HRD”, “PNG”, “ANS”)
With Worksheets(strName)
.Columns(“A:AP”).Hidden = False
.Select
.Range(“A1”).Select
End With
Next
Sheets(“Key Ratio “).Select
Range(“A3”).Select
End Sub[/codebox]

1) if I hit the OR press cancel button instead of supplying the password, the code unprotect the sheet & jump to next sheet.

2) It is more convenient for me to unprotect all sheets in array in single attempt, instead of supplying password for each sheet one by one.

3) If I supplied correct password on second attempt, the code unprotect the sheet but msg still flashes.

Sorry if it sounds irretating but I have not tested the code this way before.