Validation and active cell

[mikla]

Hi everybody,

I am working for 1 NGO in Bosnia for Demining. I almost finished one expenses follow up excel tool but I am blocked at 1 point. If someone can help, I would really appreciate...

Explanation: See below the VBA writing for validation list.
I would need to replace below the =$B33$ by an absolute reference of 1 active cell. (active cell should be located 1 row higher than the start with Range("B" & ActiveCell.Row).Select

Range("B" & ActiveCell.Row).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$33"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("B" & ActiveCell.Row).Select


Background info for this: I have different budget lines and use the "go to" same validation to select from the active cell the same budget lines on my worksheet and unhide them.

Do not know if it is possible. If you have a solution it would be great.

Michael

[Bernie Deitrick]

Try:

Formula1:="=" & Range("B" & ActiveCell.Row-1).Address

HTH,
Bernie
MS Excel MVP


"mikla" <[email protected]> wrote in message
news:[email protected]...
>
> Hi everybody,
>
> I am working for 1 NGO in Bosnia for Demining. I almost finished one
> expenses follow up excel tool but I am blocked at 1 point. If someone
> can help, I would really appreciate...
>
> Explanation: See below the VBA writing for validation list.
> I would need to replace below the _=$B33$_ by *an absolute reference of
> 1 active cell*. (active cell should be located 1 row higher than the
> start with Range("B" & ActiveCell.Row).Select
>
> Range("B" & ActiveCell.Row).Select
> With Selection.Validation
> Delete
> Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
> Operator:= _
> xlBetween, Formula1:="=$B$33"
> IgnoreBlank = True
> InCellDropdown = True
> InputTitle = ""
> ErrorTitle = ""
> InputMessage = ""
> ErrorMessage = ""
> ShowInput = True
> ShowError = True
> End With
> Range("B" & ActiveCell.Row).Select
>
>
> Background info for this: I have different budget lines and use the "go
> to" same validation to select from the active cell the same budget lines
> on my worksheet and unhide them.
>
> Do not know if it is possible. If you have a solution it would be
> great.
>
> Michael
>
>
> --
> mikla
> ------------------------------------------------------------------------
> mikla's Profile: http://www.excelforum.com/member.php...o&userid=37179
> View this thread: http://www.excelforum.com/showthread...hreadid=568938
>

[mikla]

T'anks a million. It works perfectly.

Michael