Conditional formatting based on another cell 2

[Jkaen]

Using search it seems that there isnt a way to do this, but people seem to find very clever ways of getting round the problem, so maybe you can help here.

I have made some design spreadsheets for our company that have to be used in the states as well and therefore I have built unit conversion into them. Thing is this means to fully display the diameter of say a 0.25" pipe I have to show the same decimals for a 800mm pipe.(i.e. 800.00mm) this not only looks stupid but shows unrealistic accuracy of the numbers.

Is there a way where based on unit selection (drop down menu giving target cell value of 1 for inches or 2 for mm) I can change the number of decimals displayed?

Thanks in advance

 

[gb433054]

Hi JKaen

I take it you have a drop down menu on your spreadsheet linked to a cell, outputing 1 for Imperial and 2 for Metric.

If you right click on the drop down menu and choose assign macro, this will open the change_event for that control. This means the following code will execute every time the drop down is used.

In the DropDown1_Change() sub enter the folling code

Sub DropDown1_Change()

' Test the value of the cell linked to the dropdown
' 1 for Imperial
' 2 for Metric
If ThisWorkbook.Sheets("Sheet1&quot.Range("A3&quot = 1 Then
' If imperial change the cell format for A1 to 4 deciaml places
ThisWorkbook.Sheets(2).Cells(1, 1).NumberFormat = "0.0000"
Else
' If metric change the cell format for A1 to 3 deciaml places
ThisWorkbook.Sheets(2).Cells(1, 1).NumberFormat = "0.000"
End If

End Sub

 

[Jkaen]

Thanks, I will try that.

 

[Jkaen]

Well it worked, so thanks for that, but it seems to have thrown a new problem up. Due to the fact some of my users dont really know what they are doing I have to protect my spreadsheet, and it seems that for this macro to work the target cells need to be unprotected.

Using the record on cursor thing for the macro I have found how to protect/unprotect in the macro, however it doesnt protect with a password, is there anyway to add a password as part of the condition of protect/unprotect?

 

[gb433054]

Add this line above the if statement in the dropdown_change module.

ThisWorkbook.Sheets("Mysheet&quot.UnProtect ("MyPassword&quot

Then add this line after the if statement

ThisWorkbook.Sheets("Mysheet&quot.Protect ("MyPassword&quot


Remember to set the cell properties to locked for the cells you wish to protect.

 

[Jkaen]

Yep, works great, thanks