Need to make DTPicker invisible based on another cell's value.

[Remnant]

Hello,

This is my first post here and I'm still pretty new to VBA. First of all I'm using Excel 2003. I have a small problem in which I am trying to make a DTPicker control go invisible after another cell, which simply has Validation with only active, inactive as the options, reads "INACTIVE".

IE: Cell linked to DTPicker - E4, Cell with validation drop-down box - I4.

Any help at all would be greatly appreciated! Thanks!

[Zack Barresse]

Hi there, welcome to the board!

You can use something like this as your worksheet change event...
Option Explicit

Const sCheck As String = "I4"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range(sCheck)) Is Nothing Then Exit Sub
If UCase(Target.Value) = "INACTIVE" Then
Me.DTPicker1.Visible = False
Else
Me.DTPicker1.Visible = True
End If
End Sub

[Remnant]

Hi there, welcome to the board!

You can use something like this as your worksheet change event...
Option Explicit

Const sCheck As String = "I4"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range(sCheck)) Is Nothing Then Exit Sub
If UCase(Target.Value) = "INACTIVE" Then
Me.DTPicker1.Visible = False
Else
Me.DTPicker1.Visible = True
End If
End Sub

Thanks for the quick response! The code works, however there seems to be a glitch happening after I close the spreadsheet and re-open with it being invisible. It isn't retaining it's size or aspect ratio, even with it locked. Is there a way I can actually embed it inside the cell?

[Zack Barresse]

It's probably a display issue. Scroll your screen down and then back up. Does that fix it? The control shouldn't actually resize on its own.

[Remnant]

It's probably a display issue. Scroll your screen down and then back up. Does that fix it? The control shouldn't actually resize on its own.

Sorry, I would have said that but I can't find the edit button and didn't want to double-post. Scrolling down fixes it. However, when I scroll down I'd like for the control to not stay on top of the page break where I have frozen the panes. I know I'm just nit-picking. If it isn't possible it's no big deal. Thanks again for the help! It's nice to find another newb-friendly forum. **EDIT** There it is!

[Zack Barresse]

Then you need your control to not be over the top of the range which is frozen. I'm sure we could code around your scrolling, or even a selection made, but it seems way over the top. Best solution is to just place it below the frozen part.

I'm really glad you like the forum. :)

[Remnant]

Then you need your control to not be over the top of the range which is frozen. I'm sure we could code around your scrolling, or even a selection made, but it seems way over the top. Best solution is to just place it below the frozen part.

I'm really glad you like the forum. :)

Actually, that's what I mean. The control is well below the range that is frozen, but when I scroll down and the control moves up, it goes on top of the freeze pane, almost like it has an "always on top" option. It may be an issue with the version I'm using. Thanks, though.

[Zack Barresse]

I can't reproduce the error. Can you post a sample file?