How to add A Data Validation Input Message in VBA

A

adrianh

New Member
Joined
Sep 13, 2009
Messages
29
Cells A1 to A10 contain names of different cars,
what i would like to do in VBA is when i select any individual cell to the right of these the name of the car appears as a message (as if i had gone DATA -> VALIDATION -> INPUT MESSAGE -> CARS NAME).
Tried positioning a Userform next to the active cell but when i scroll down the page and select a cell the userform is to far down the screen to be seen.
Alternatively this could be done using comments. Any help would be appreciated.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
you can experiment with this code to force the userform into the proper location:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Show vbModeless
UserForm1.Move Target.Left + (Target.Width * 2), Target.Top + UserForm1.Height

End Sub
 
Upvote 0
Thanks for the post, tried your code but as soon as i scroll down the page and select another cell the userform gets further and further down the screen untill it disapears
 
Upvote 0
Why not use the data validation input message ... ? Seems just what you need here.

Alex
 
Upvote 0
Yes Alex, that's what i would like to do but don't know how to enter the data validation message for the type of car using vba, also if the data in A1 to A10 changes the data validation message in any coresponding cell would need changing automatically upon that cell been selected
 
Last edited:
Upvote 0
The data validation message is based on the source range you specify for the validation. You do this on the first tab in data validation - allowing input from the list which is identified as the values in A1:A10.

No need for vba there.

Alex
 
Upvote 0
Sorry, looks like I'm thinking of the drop down, not the input message. So use the drop down. :)

Alex
 
Upvote 0
Not quite following here Alex, just looked at DATA->VALIDATION->SETTINGS tab and a bit confused as what to do. If this helps anymore, what i have in column A is car names, columns B,C,D etc are to represent the 52 weeks of the year, when i select any cell (column B onwards) i would like a little message by the side of the selected cell to say what car it is.
 
Upvote 0
you could change the excel caption. probably the quickest fix besides freezing the column.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Caption = Target.Value 'or whatever cell value you want to show
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
What is the difference between [INPUT MESSAGE] and [ERROR ALERT] in data validation?
Replies
1
Views
123
6StringJazzer
6StringJazzer
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
179
still learning
S
Z
  • Solved
Excel VBA: Using OR with <>
Replies
3
Views
133
rlv01
R
A
  • Question
How can I ensure that users enter only numeric values and display an error message if they input non-numeric characters? Additionally, is there a way
Replies
2
Views
441
Fluff
Fluff
N
  • Solved
VBA Question
Replies
3
Views
149
RoryA
RoryA

Forum statistics

Threads
1,215,543
Messages
6,125,429
Members
449,223
Latest member
Narrian

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top