message box to return cell values

burdie87

Board Regular
Joined
May 25, 2005
Messages
152
Hi All,
If I have information in several cells, say for the sake of argueing a1 through 5 and i want a message box to appear displaying the info from those cells when i push a button in a userform. How would I go about doing this?
I was going to originally use a userform to display this info by using text boxes but i found that doing this the userform ended up wiping my formulas from my work sheet and as I use another userform to change values which effect the information that will be displayed I need the formulas to keep the document dynamic. So it would seem a message box would be better.

Any help would be great.

Regards
Alan.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

gschwint

Board Regular
Joined
Aug 17, 2004
Messages
121
If you double click the command buttin in the user form you can enter the code you want to run when the button is clicked. There you can put in the message box code you want.

George
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Something like this maybe:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
        
        MsgBox _
            [A1].Value & vbCrLf & _
            [A2].Value & vbCrLf & _
            [A3].Value & vbCrLf & _
            [A4].Value & vbCrLf & _
            [A5].Value, vbInformation & vbOKOnly, "Cell Values"
            
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152
I have modified your code to the following but get an error any ideas??
Code:
Private Sub CommandButton1_Click()
  MsgBox _
            [Aluminium Input!C15].Value & vbCrLf & _
            [Aluminium Input!E15].Value & vbCrLf & _
            [Aluminium Input!F15].Value & vbCrLf & _
            [Aluminium Input!G15].Value & vbCrLf & _
            [Aluminium Input!H15].Value, vbInformation & vbOKOnly, "Cell Values"
            
End Sub

All Help is gratefully recieved.

Al
 

gschwint

Board Regular
Joined
Aug 17, 2004
Messages
121

ADVERTISEMENT

Try this:
Code:
Private Sub CommandButton1_Click() 
  MsgBox _ 
            Sheets("Aluminium Input").range("C15").Value & vbCrLf & _ 
            Sheets("Aluminium Input").range("E15").Value & vbCrLf & _ 
            Sheets("Aluminium Input").range("F15").Value & vbCrLf & _ 
            Sheets("Aluminium Input").range("G15").Value & vbCrLf & _ 
            Sheets("Aluminium Input").range("H15").Value, vbInformation & vbOKOnly, "Cell Values" 
            
End Sub

George
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
With a space in the sheet name you need apostrophes:
Code:
['Aluminum Input'!C15]

Smitty
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152

ADVERTISEMENT

That is brill folks. one quick question if I want to put a fixed text in front of the variable say "Cheese shape:" then the value that the box currently displays how do i go about coding that.??
Sorry for all the questions :(

Regards
Al
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Something like this:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
  <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
  
    x = "Cheese Shape "
    
  MsgBox _
            x & [<SPAN style="color:#007F00">'Aluminium Input'!C15].Value & vbCrLf & _
            x & ['Aluminium Input'!E15].Value & vbCrLf & _
            x & ['Aluminium Input'!F15].Value & vbCrLf & _
            x & ['Aluminium Input'!G15].Value & vbCrLf & _
            x & ['Aluminium Input'!H15].Value, vbInformation & vbOKOnly, "Cell Values"</SPAN>
            
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

(Note that if this is going to get anymore complicated with the number of variables, then this code isn't going to be real efficient).
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152
Hey smitty,

if I use your code like below will it be ok.
Code:
Private Sub CommandButton1_Click()
  Dim x As String
  Dim y As String
  Dim z As String  
  Dim w As String
  Dim v As String
  
    x = "Cheese Shape "
    y = "Beans"
    z = "Soup"
    w = "Peas"
    v = "carrots"

  MsgBox _
            x & ['Aluminium Input'!C15].Value & vbCrLf & _
            y & ['Aluminium Input'!E15].Value & vbCrLf & _
            z & ['Aluminium Input'!F15].Value & vbCrLf & _
            w & ['Aluminium Input'!G15].Value & vbCrLf & _
            v & ['Aluminium Input'!H15].Value, vbInformation & vbOKOnly, "Cell Values"
            
End Sub

Regards
Alan
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,385
Members
412,590
Latest member
Velly
Top