• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to activate pop-up w/ data from within a worksheet when clicking on a cell

W

Wild_Weisle

New Member
What is a good VBA code to use for the following:


I have a worksheet (OCT), when user clicks on a cell within a certian range (R8:R57), it will generate a pop-up window containing data found in the following cells (A60:A84) and (I60:I84).
 
Hi, Wild_Weisle!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Give a look to this file:

https://dl.dropbox.com/u/60558749/VBA%20to%20activate%20pop-up%20w_%20data%20from%20within%20a%20worksheet%20when%20clicking%20on%20a%20cell%20%28for%20Wild_Weisle%20at%20chandoo.org%29.xlsm


There are three named ranges defined (TriggerRange, DisplayRange1 and DisplayRange2) in sheet OCT, and the involved code for the Worksheet_Change event is this:


-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksTriggerRange = "TriggerRange"
Const ksDisplayRange1 = "DisplayRange1"
Const ksDisplayRange2 = "DisplayRange2"
' declarations
Dim rngT As Range, rngD1 As Range, rngD2 As Range
Dim c As Range, A As String
' start
Set rngT = Range(ksTriggerRange)
If Application.Intersect(Target, Range(ksTriggerRange)) Is Nothing Then Exit Sub
Set rngD1 = Range(ksDisplayRange1)
Set rngD2 = Range(ksDisplayRange2)
' process
'  retrieve data
A = ""
'   first range
A = A & vbCrLf
A = A & "Data in range: " & rngD1.Address(False, False, xlA1) & vbCrLf
For Each c In rngD1
A = A & c.Value & vbCrLf
Next c
'   second range
A = A & vbCrLf
A = A & "Data in range: " & rngD2.Address(False, False, xlA1) & vbCrLf
For Each c In rngD2
A = A & c.Value & vbCrLf
Next c
'  show data
MsgBox A, vbApplicationModal + vbDefaultButton1 + vbInformation, "Data found"
' end
Set rngT = Nothing
Set rngD1 = Nothing
Set rngD2 = Nothing
End Sub
[/pre]
-----


Give a try and just advise if any issue.


Regards!
 
Good evening SirJB7

tried to open your link but error message say unable to find file ???
 
Hi, b(ut)ob(ut)hc!

Thanks for the warning, problem solved, dropbox full, deleted old things and now available.

Regards!
 
SirJ87,


Thank you for the pointers. I attempted to locate a posting similar to my needs; but, was unssuccessful. I'll be sure to read the recommended postings you've identified.


About your recommended solution:


First, I need to declare that my knowledge on VBA code is very limited. I would classify myself as "a very weak amateur". Your solution is very close to what i am looking for. However, when i posted the code into my worksheet, it errors out on the following line:


Set rngT = Range(ksTriggerRange)


Also, I noticed in your worksheet that you have to input a value into one of the cells (R8:R57) to get the pop-up. Is there a way to have the pop-up appear when you click on the cell so that the information is available prior to populating the cell? Basically, I am wanting the pop-up to present a list of optional values to place in the cell for the end user.


Respectfully,

Wild_Weisle
 
Hi, Wild_Weisle!


Don't worry about your VBA skills, everyone here is learning as I'm doing too.


A question about the error: it appears when you use your downloaded copy of my uploaded workbook, or when you copied the code into other workbook?


If first, I've checked it again and it works fine. If second, I guess you haven't defined the three named ranges TriggerRange, DisplayRange1 and DisplayRange2 (see definition ranges in the uploaded workbook, they refer to the yellow shaded cells).


It was popping up when you updated a cell in range R8:R57 as I misread your requirement. Sorry. Now I updated it to respond to Selection_Change event instead of Change event (clicking or moving with arrow keys, instead of modifying). But take care that if you click or select a cell in that range, enter a value or a formula, and press <Enter>, next cell in column will be selected and message popped up again; and that's unavoidable unless you enter a value and move out thru left or right with arrow keys instead of pressing <Enter>.


Just download again the file from same link, and advise if any issue.


Regards!
 
Thank you, this worked perfectly.


One last request if possible...


how can i increase the msgbox size to be 45 inside this code?


Thanks.
 
Hi, Wild_Weisle!


Just changing the named range definitions for ranges DisplayRange1 and DisplayRange2. But take care of MsgBox height, if you keep the format of showing each cell value in a new line; it might be too great. Give a try and see how it works.


And glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


Regards!
 
Hi Sir - JB007,


Please teach me what is the vbApplicationModal?


Code: 
MsgBox A, vbApplicationModal + vbDefaultButton1 + vbInformation, "Data found"


As I don't think a message box can be vbModeless
, and user can select the the outer area.. Is it ?..


@Wild_Weisle

If it possible to change the code, then change vbTab instead of vbCrLf, it will decrease the msgbox size little bit.. :)

but you cant set the size of msgbox, it increase / decrease according to the text in it..
 
@Debraj Roy


Hi!


MsgBox syntax is:

MsgBox(prompt[, buttons][, title][, helpfile, context])


Where,

prompt: (required) string expression to be displayed in the dialog

buttons: (optional) codes to build an unique number that tells VBA how to behave -type MsgBox at Immediate window pane, position the cursor over it, press F1 and go to built-in Excel help in your language, mine would be in Spanish-)

title: (optional) string expression to be displayed in the title bar

helpfile: (optional) string expression that identifies the help file used to provide interactive help in the dialog

context: (optional) numeric expression equal to context help number assigned by the author to the related help topic


Two of the item values for 'buttons' are:

- vbApplicationModal, which forces the user to respond to the dialog message before being able to go on working with the actual application (Excel in this case)

- vbSystemModal, which suspends all applications until the user responds to the dialog message


That's to say:

a) if none set the user can switch to other sheets of current workbook, to other workbooks of current Excel instance, or to any other applications, without or before responding the MsgBox

b) if vbApplicationModal set the user cannot switch to neither other sheets of current workbook nor other workbooks of current Excel instance, but can switch to any other applications, without or before responding the MsgBox

c) if vbSystemModal set the user cannot do nothing but responding the Msgbox


vbDefaultButtonX sets which button get the focus when MsgBox displayed.


vbInformation/vbQuestion/vbCritical sets the icon to be displayed into the MsgBox and the sound that's played.


vbOkOnly/vbOkCancel/vbAbortRetryIgnore/vbYesNoCancel/vbYesNo/vbRetryCancel sets the number and type of buttons displayed.


Hope it helps, if not please advise.


Regards!
 
Code: 
MsgBox ("Thanks again for the edited version..",vbOKOnly,"With lots of Respect :)")
 
You must log in or register to reply here.
Back
Top