MsgBox Help

rockchalk33

Board Regular
Joined
Jan 12, 2016
Messages
110
Hey all,

Thanks for your time in helping me.

I have a list of names populating each row in column A

In each row there are dates corresponding to the name in column A

Some names have zero dates, some names have up to forty dates

Example:

name1 date1 date2 date3
name2
name3 date4 date5
name4 date6

I need a msgbox to show in bullet points from top to bottom, each date for the particular name that was selected.

I am just a little confused on the msgbox part with a "dynamic date range" as well as bullet points

Thanks for your help!!!

Devin
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
Give this event code a try...
Code:
[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim LastCol As Long
  LastCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
  If Target.Column = 1 And Target.Count = 1 Then
    If Len(Target.Value) Then
      If LastCol = 2 Then
        MsgBox ChrW(8226) & " " & Cells(Target.Row, "B").Value
      ElseIf LastCol > 2 Then
        MsgBox ChrW(8226) & " " & Join(Application.Index(Range(Target.Offset(, 1), Cells(Target.Row, LastCol)).Value, 1, 0), vbLf & ChrW(8226) & " ")
      End If
    End If
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:

rockchalk33

Board Regular
Joined
Jan 12, 2016
Messages
110
Give this event code a try...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim LastCol As Long
  LastCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
  If Target.Column = 1 And Target.Count = 1 Then
    If Len(Target.Value) Then
      If LastCol = 2 Then
        MsgBox ChrW(8226) & " " & Cells(Target.Row, "B").Value
      ElseIf LastCol > 2 Then
        MsgBox ChrW(8226) & " " & Join(Application.Index(Range(Target.Offset(, 1), Cells(Target.Row, LastCol)).Value, 1, 0), vbLf & ChrW(8226) & " ")
      End If
    End If
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


Thank you so much! This appears to be exactly what I needed!!!
 

Eddiejon

New Member
Joined
Jan 14, 2019
Messages
2
Hi all,

I have a similar request for help regarding msgbox and activex checkbox controls

Basically I have a workbook with several worksheet, each worksheet is used to guide one through a series of "change control processes". Each process asks a question, the answer can be either a text input or requires 'Yes' via checkbox (nil is the default).

At the moment this document is very flat (similar to printing a form and filling it out by hand, except its an excel file that we save for our records), but it does contain all the appropriate
change control process requirements. I have been adding a little bit of dynamism with formulas, conditional formatting and a bit of VBA. When I say VBA, I mean bits and pieces I have gathered from the web and mostly from this forum, I am a novice.

Activex Checkbox now hides and unhides rows, as required. https://www.mrexcel.com/forum/excel-questions/23889-hide-unhide-rows-checkbox.html code by NateO, brilliant thanx.

Changed NateO's code below

Rich (BB code):
Private Sub CheckBox1_Click()If CheckBox1 = True Then


    [10:11].EntireRow.Hidden = False


    Else: [10:11].EntireRow.Hidden = True


End If


End Sub


to

Rich (BB code):
 Private Sub CheckBox1_Click()If CheckBox1 = True Then


    Sheets("Sheet3").Visible = True


    Else: Sheets("Sheet3").Visible = False


End If


End Sub

Now I can turn hide and unhide worksheets, woohoo we're getting some where. Then one gets ambitious and it all goes wrong.

Below is a simple example of excel file, with the type of question and answer options.
So if I tick the checkbox in C2, Sheet 3 appears, cell D2, conditional formatting removes colour and the word "Exception" appears, plus explanation. The checkbox in E2 is always visible and always usable, this will cause an issue as it can be selected without C2 first being selected. E2 checkbox also hide/unhides Sheet2.

Would it be possible for code to hide checkbox E2 without hiding the row or column? Hide when C2 checkbox is false, unhide when ticked.
Would it be possible for code to show msgbox with "blah blah blah" when Ckeckbox C2 is true and same of checkbox E2.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Customer-Specific Product</td><td style=";">Yes</td><td style="text-align: right;;">##</td><td style="text-align: right;;">Exception</td><td style="text-align: right;;">##</td><td style="text-align: right;;"></td><td style=";">Simple change may be used only where it is not a customer specific product that is subject to international standards procedures or where it is a spelling or numerical error in drawing or document that causes no physical change to product or process or ERP system updates with no effect on cost</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">C2=TRUE,"Exception",""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IF(<font color="Blue">D2="exception",K2,""</font>)</td></tr></tbody></table></td></tr></table><br />

Thanx in advance
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,336
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top