Hiding Drop Down Lists

[mike&jess]

Helloo,

I would like to create an automatic and manual setting in my latest spreadsheet.
The operation mode is a drop down list. When Automatic Input is selected, then the engine type and load will be a drop down list also. However, when the Operation Mode is in Manual Input, is there a way of removing the drop down box allowing people to type values manually, and for later relaed formulas to still recognise this?

Thanks!
[TABLE="width: 405"]
<tbody>[TR]
[TD][/TD]
[TD]Operation Mode[/TD]
[TD]Manual Input [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Engine Type[/TD]
[TD]JMS 320[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Load (%)[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[AOB]

Try this as a starting point - needs to be added to the worksheet itself rather than in a separate module...

Have assumed your example below is in cells A1 to B3 (i.e. your labels are in column A and your existing drop-downs are in column B) and the drop-down lists are in named ranges somewhere in the workbook (have called them 'EngineTypeList' and 'LoadList') - you can tweak to suit your needs...

Private Sub Worksheet_Change(ByVal Target As Range)[INDENT]
Dim rgOpMode As Range
Dim rgEngineType As Range
Dim rgLoad As Range
    
Set rgOpMode = Me.Range("B1")
    
If Target.Count <> 1 Or Target.Address <> rgOpMode.Address Then End
    
Set rgEngineType = Me.Range("B2")
Set rgLoad = Me.Range("B3")
    
If rgOpMode = "Manual" Then
        [/INDENT]
[INDENT=2]
rgEngineType.Validation.Delete
rgLoad.Validation.Delete[/INDENT]
[INDENT]
ElseIf rgOpMode = "Automatic" Then
        [/INDENT]
[INDENT=2]
With rgEngineType.Validation[/INDENT]
[INDENT=3].Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=EngineTypeList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True[/INDENT]
[INDENT=2]End With
        
With rgLoad.Validation[/INDENT]
[INDENT=3].Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=LoadList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True[/INDENT]
[INDENT=2]End With[/INDENT]
[INDENT]
End If


[/INDENT]
End Sub