VBA Data Validation - Input Message

[Pinaceous]

Hi All,

I'm working with a table where I've set an Input Message box to appear in each cell of a table.

Now my question is, can I by VBA designate the Input Message box to a specific area on the spread sheet?

I think the Input Message box is important, but am finding it annoying at times in having it appear as it does in following the cells as it does.

Thank you.
-Pinaceous

 

[Trebor76]

Hi Pinaceous,

Try this worksheet event macro (i.e. has to go on the sheet you want the code to run not in a standard module):

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Application.EnableEvents = False
            'Pinaceous - your input box code goes here
        Application.EnableEvents = True
    End If

End Sub

I used the range A1:A10 to create the macro. Change the range to suit your needs.

HTH

Robert

 

[Pinaceous]

Hi Robert,

Thanks for the code I can't wait to test it out!

I have one question, before I try out your code. If in applying your code as is, it looks column input message box specific.

So if I extend my range I'll have to additionally add it to where you indicated it in your code, right?

If that's the case that's perfect!

Thank you again for providing your code!

Paul

 

[Trebor76]

If in applying your code as is, it looks column input message box specific.
So if I extend my range I'll have to additionally add it to where you indicated it in your code, right?

Once you put in your existing InputBox code where I have said in my code above it will execute wherever cells A1 to A10 are selected. Just change that range in the code to suit.

Hope that helps??

Robert

 

[Pinaceous]

Hi Robert,

Thanks again, let me give it a go and I'll post you back later on.

 

[Pinaceous]

Hi Robert,

If in

Target, Range("A1:A10")

I have a Data Validation - Input Message that says "TIME"; how would I write it into your code?


I've tried to write

 Time 'Pinaceous - your input box code goes here

but I get an error.

Can you repost your code with this variable?

If you mean:

MsgBox ("TIME")

What I am referring to is on the ribbon tab under Data Validation then Input Message, that box there.
Thank you!
Paul

 

[mikerickson]

Why are you going with VBA? Just set Data Validation with the appropriate input message on the cells that you desire. Data Validation is very flexible about which cells have what validation.

If the input message is the only part of DV that you want (i.e. if any input is acceptable) just use the validation formula =TRUE

 

[Pinaceous]

Hi mikerickson,

Thank you for your post. What I am trying to do is permanently affix a desired location for the data validation input message.

Can you please explain more on your validation formula? Do I simply put your formula it in a designated cell and the input message of the selected data validation cell will appear there? If that's the case that is what I want and will have to play with that.

I am able to move the input message box to another location but I cannot save it there upon exiting then reopening the doc. I've even tried to record a macro of its movement but it does nothing.

Thanks,
Paul

 

[mikerickson]

When you set the Data Validation on a cell, one of the options is to use a formula to decide if the user's entry is valid. =TRUE says that any entry is valid.
For your purpose of giving a input instruction message every time that the cell is selected, the only reason for using Excel's native Data Validation is to trigger that message.

 

[Pinaceous]

Formatting the Data Validation Message Box



This thread edited by Jaafar really completes this idea that I've asked to a whole other level of thinking.

I'm really impressed on how much talent there is in this forum and I'm very glad to be apart of it.