Multiple variables in MsgBox

smiffy197

New Member
Joined
Aug 21, 2015
Messages
4
Hi There,

I am trying to get a pop up message box to display a list of names that may change each time you open the sheet.

I deal with new starters at work and want to have a popup box that finds the users who are starting in the next 2 weeks and haven't already been assigned a laptop. I have the code sorted to the point where I have a list of these people on a separate sheet, but I want a MsgBox to display them like this:

The following users haven't been assigned laptops:

Firstname Lastname
Firstname Lastname
etc.

I can get the message box to display the first name on the list but don't know how to add the rest. Any help would be appreciated because I have spent a couple of days on this now and got no where!

Many Thanks
J
 

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,126
Something like this perhaps:

Code:
-

MsgBox "The following users haven't been assigned laptops:" & vbNewLine & Firstname & " " & Lastname & vbNewline & Firstname & " " & Lastname & vbNewline ....


-
 

smiffy197

New Member
Joined
Aug 21, 2015
Messages
4
Thanks dreid,

That's pretty much as far as I have got at the moment but the names are variables based on cell references and the number of names might change each time

Sometimes you'll open the sheet and have 6 people sometimes only one or two.

The way you suggested, which is a method I have tried leaves lots of blank space in the MsgBox if there is only one persons name.
 

smiffy197

New Member
Joined
Aug 21, 2015
Messages
4
Managed to get this working.
This is the bit of the code I was stuck on.

Code:
 Dim c As Range
                Dim strng As String
        
                    Set c = Range("A2").End(xlDown)
                    k = 1
                    Do
                        k = k + 1
                        strng = strng & Cells(k, 4) & " " & Chr(13)
                    Loop Until k = c.Row
                    
                    '//Displays message for multiple users
                    MsgBox "The following users will start soon and have not been assigned laptops: " & vbNewLine & vbNewLine & strng, vbInformation, "Welcome"
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top