PDA

View Full Version : VBA using IF to populate msgbox



Notsure
07-28-2015, 05:51 AM
Greetings. I am a beginner at VBA and need some assistance. On sheet1 I have a command button. When I enter a number in Cell B3 and click the button, I want a msgbox to pop up with the text in a cell from sheet2. For example: I enter 16 into sheet1 cell B3, so on sheet 2 cell A16 has the comment "hit reset". I want the msgbox to populate with that comment. There will be a range of comments to choose from on sheet2 ("A1:A100"). If a number in B3 equals the number on sheet2 A1 thru A100 then populate the msgbox. If B3 equaled 17 then msgbox would say "power down" (that comment would be stored on sheet2 cell A17). Any help would be appreciated!

jonh
07-28-2015, 07:21 AM
Sub DisplayMessage()
Dim s As String, i As Variant

'get the row number
i = Range("B3")

'check entered value is a number
If IsNumeric(i) Then

'get the text
s = Sheets("sheet2").Cells(i, 1).Text

If Len(s) Then
'display the text
MsgBox s, vbInformation
Else
'no text was found. cell is empty
MsgBox "No text found for that number", vbExclamation
End If
Else
'invalid input
MsgBox "Number expected", vbExclamation
End If
End Sub

Notsure
07-28-2015, 07:42 AM
Sub DisplayMessage()
Dim s As String, i As Variant

'get the row number
i = Range("B3")

'check entered value is a number
If IsNumeric(i) Then

'get the text
s = Sheets("sheet2").Cells(i, 1).Text

If Len(s) Then
'display the text
MsgBox s, vbInformation
Else
'no text was found. cell is empty
MsgBox "No text found for that number", vbExclamation
End If
Else
'invalid input
MsgBox "Number expected", vbExclamation
End If
End Sub

Notsure
07-28-2015, 07:42 AM
Thanks jonh! That's exactly what I was looking for!

Notsure
07-28-2015, 08:12 AM
I also would like to do the same process but for a different button. Sheet1 cell B5 would be more of a search field, so it could pull the answer that best matches the most words. Example: in B5, a person would type- "can't get machine to start up", now the code will search sheet2 A1 thru A100 and the msg box will pop up with which ever msg has the most matched words from the sheet1 B3 cell. (If sheet2 A72 had "machine start up failure, hit reset" then that would most likely populate in the msgbox.