MsgBox Help

[rockchalk33]

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

 

[Rick Rothstein]

Give this event code a try...

[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.

 

[rockchalk33]

Give this event code a try...

[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]

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

Private Sub CheckBox1_Click()If CheckBox1 = True Then


    [10:11].EntireRow.Hidden = False


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


End If


End Sub

to

 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.

Book1
	A	B	C	D	E	F	G	H	I	J	L
1
2	Customer-Specific Product	Yes	##	Exception	##		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
3
4
5
Sheet1

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

Thanx in advance