message box to return cell values

[burdie87]

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.

 

[gschwint]

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]

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]

I have modified your code to the following but get an error any ideas??

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]

Try this:

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]

With a space in the sheet name you need apostrophes:

['Aluminum Input'!C15]

Smitty

 

[burdie87]

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]

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]

Hey smitty,

if I use your code like below will it be ok.

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