Dialog Box to display group of cells ?

[Phil Ridley]

Hi all,
I am trying to create a macro that will, on completion, display an area of cells from the worksheet so the user can accept the results of other macros that are run.

Unfortunatly, I have no idea where to start with this. Could someone offer some advice ?

Thanks in advance,
Phil.

 

[Derek]

Hi Phil

Not really sure I understand what you are trying to achieve. I am assuming you want to draw the attention of the user to a specific part of the worksheet. This macro will take a picture of a range A1:F16 and present it on screen with a message. Adapt it to your requirements if it suits

Range("A1:F16").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("C18").Select
ActiveSheet.Pictures.Paste.Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 42
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 1.5
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 18
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Range("A1").Select
MsgBox "A picture of the relevant area of the worksheet is show in the green box." & vbCrLf & "This may be dragged aside, or selected and deleted"
End Sub


regards
Derek

 

[Phil Ridley]

Thanks for that, it is very close to what I want. Is it possilbe to mount this picture in a message box, as opposed to being on the sheet ?

Phil.

 

[Derek]

Hi Phil
I don't really know, I just did a bit of experimenting using the macro recorder. You could change the text of your message box to say that the picture will delete when the user clicks OK. Then add the code for selecting and deleting the picture after the code for the message box. Experimenting with the macro recorder will help you with the code. You might be able to put this picture into a cell comment afterwards. You will find previous posts on pictures in comments here if you use the search function.
Unfortunately I have to vacate my office now.
Good luck
regards
Derek