MsgBox Help

rockchalk33

Board Regular
Joined
Jan 12, 2016
Messages
111
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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:
Upvote 0
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!!!
 
Upvote 0
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.


Cell Formulas
RangeFormula
D2=IF(C2=TRUE,"Exception","")
G2=IF(D2="exception",K2,"")


Thanx in advance
 
Upvote 0

Forum statistics

Threads
1,222,196
Messages
6,164,518
Members
451,900
Latest member
lamski

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