Vba MsgBox - Question

[Cuzzaa]

Hi guys

I am using the below code to import a specific range of data from another spreadsheet into my current workbook I have open. The below code works perfectly.

However, what I am trying to achieve is to amend the code so that my current Msg box presents the following:

Instead of simply just saying 'BOM Import Successful' I would like it to say 'BOM Import Successful' and then on a new line I'd like to add some text, for e.g. 'x number' of items were not found. X number being the number of occurrences that contain the text 'Item Not Found' in data range J14:J64 (sheet is called 'Dashboard').

However, if the count finds 0 occuraces of 'Item Not Found' in data range J14:J64 then I would like the Msg box to simply say the following:

'BOM Import Successful' and then on a new line underneath 'All items were successfully imported!'

Is anyone able to help amend my code please?

Sub BOMTest()


' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook


' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)


Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)


targetSheet.Range("F14", "I48").Value = sourceSheet.Range("A2", "K48").Value


' Close customer workbook
customerWorkbook.Close


targetSheet.Range("F7").Value = customerFilename


MsgBox "BOM Import Successful!", vbInformation
End Sub

 

[nemmi69]

Try this

Sub x()
Dim NumItems As Integer


NumItems = 4
MsgBox "BOM Import Successful!" & vbNewLine & NumItems & " number' of items were not found.", vbInformation,ThisWorkbook.Name
End Sub

 

[Yongle]

another option for you to test

[I][COLOR=#006400]'declare variables[/COLOR][/I]
Dim aCount As Integer, msg As String
Const msg1 = "BOM Import Successful" & vbCr
Const msg2 = " items not found"
Const msg3 = "All items were successfully imported!"


[COLOR=#006400][I]'use count to determine message string[/I][/COLOR]
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), "Item Not Found")
If aCount = 0 Then msg = msg1 & msg3 Else msg = msg1 & aCount & msg2
MsgBox msg

 

[Cuzzaa]

another option for you to test

[I][COLOR=#006400]'declare variables[/COLOR][/I]
Dim aCount As Integer, msg As String
Const msg1 = "BOM Import Successful" & vbCr
Const msg2 = " items not found"
Const msg3 = "All items were successfully imported!"


[COLOR=#006400][I]'use count to determine message string[/I][/COLOR]
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), "Item Not Found")
If aCount = 0 Then msg = msg1 & msg3 Else msg = msg1 & aCount & msg2
MsgBox msg

@Yongle & @nemmi69 thank you for both of your suggestions.

I can't seem to get either to work, would you mind copying my existing vba code with your suggestions implemented please? I think I'm putting them in the wrong locations within my code.

Thank you so much

 

[nemmi69]

Is this code sitting in a module?

Seems to work fine for me.

 

[Cuzzaa]

Is this code sitting in a module?

Seems to work fine for me.

Hi, yes it is. This is my code below assigned to a button:

Sub BOMTest()


' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook


' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)


Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)


targetSheet.Range("F14", "I48").Value = sourceSheet.Range("A2", "K48").Value


' Close customer workbook
customerWorkbook.Close


targetSheet.Range("F7").Value = customerFilename


MsgBox "BOM Import Successful!", vbInformation
End Sub

Sorry, but where do I place your code within my code above for it to all work together?

 

[Cuzzaa]

Hi,

I have managed to get the below code to work - thank you!

Sub testoday()




' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


Dim aCount As Integer, msg As String
Const msg1 = "BOM Import Successful" & vbCr
Const msg2 = " items not found"
Const msg3 = "All items were successfully imported!"






' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook




' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)




Set customerWorkbook = Application.Workbooks.Open(customerFilename)




' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)




targetSheet.Range("F14", "I48").Value = sourceSheet.Range("A2", "K48").Value




' Close customer workbook
customerWorkbook.Close




targetSheet.Range("F7").Value = customerFilename




aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), "Item Not Found")
If aCount = 0 Then msg = msg1 & msg3 Else msg = msg1 & aCount & msg2
MsgBox msg
End Sub

Please can I ask this, instead of counting the number of instances of 'Item Not Found' in this cell range, please could you help me instead with the code of counting the number of instances which contain a numerical value greater than £0? (ignoring text values).

Is that possible?

 

[nemmi69]

Sub BOMTest()




' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim aCount As Integer, msg As String
Const msg1 = "BOM Import Successful" & vbCr
Const msg2 = " items not found"
Const msg3 = "All items were successfully imported!"




' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook




' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)




Set customerWorkbook = Application.Workbooks.Open(customerFilename)




' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)




targetSheet.Range("F14", "I48").Value = sourceSheet.Range("A2", "K48").Value




' Close customer workbook
customerWorkbook.Close




targetSheet.Range("F7").Value = customerFilename
aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), "Item Not Found")


If aCount = 0 Then msg = msg1 & msg3 Else msg = msg1 & aCount & msg2
MsgBox msg
End Sub

 

[Cuzzaa]

Thank you @nemmi69

Please can I be a pain...

Please can I ask this, instead of counting the number of instances of 'Item Not Found' in this cell range, please could you help me instead with the code of counting the number of instances which contain a numerical value greater than £0? (ignoring text values).

Is this possible?

 

[Yongle]

Please can I ask this, instead of counting the number of instances of 'Item Not Found' in this cell range, please could you help me instead with the code of counting the number of instances which contain a numerical value greater than £0? (ignoring text values).

amend line that provides aCount to this

aCount = WorksheetFunction.CountIf(Sheets("Dashboard").Range("J14:J64"), ">0")