MsgBox Required depends on selection

[Anbuselvam K]

Dear Excel Genius
I have RM01 to RM100 cost in RM Purchase sheet like the below image. Target cells are C8:CX13


When I select any of one cell in the target range it has to show the msgbox from the sheet RM Cost from column A to lastcol


For example, If I select the sheet RM Purchase cell "D13" SAR 6.70 and the cost coming from above snapshot "K8"

MsgBox should show like-below

Date 06-Jun-2023
RM Code CGF01
RM Name 401
.
.
.
.
Total Cost SAR 112,562.342

(Target value intersects row 2 code "CGF01", row 3 real name "401", date from column "B" is 06-Jun-2023 need to match with sheet RM Cost Column 2, 3, and 1 respectively)

I added only D13 and E13 details in the RM Cost sheet.

I am expecting your valuable reply. Thanks in advance.

 

[BobBridges]

Let's see whether I understand this correctly:

1) An operator looks at worksheet "RM Purchase".
2) The operator selects a cell somewhere in the range C8:GT12 (or however many rows in that range).
3) Let's say the operator selects CU11. The program should look at cell CU11, and see the value 5.1.
4) The program looks for 5.1 in col K of the other worksheet, "RM Cost".
5) Assuming it finds a matching value in col K, the program then displays the values that are in A:C of that row.

Is that what you're after?

And how much do you know about writing a program in Basic? It won't be hard, but you have to at least be able to learn something about it.

 

[Anbuselvam K]

@BobBridges First, Thanks a lot for your reply.

1) An operator looks at worksheet "RM Purchase".

Yes, Correct

2) The operator selects a cell somewhere in the range C8:GT12 (or however many rows in that range).

C8:CX13 as after the column "CX" is not the price.

3) Let's say the operator selects CU11. The program should look at cell CU11, and see the value 5.1.

No, If the operator selects "CU11" then it has to look at the intersect values of the same sheet "CU2" and "CU3" and the date of "B11" to the RM Cost sheet Columns 2, 3, and 1.

Sheet "RM Purchase" CU2 "WW97"
Sheet "RM Purchase" CU3 "Name97"
Sheet "RM Purchase" B11 "24-Jan-2023"

These 3 data are not found in the sheet "RM Cost" Columns 1, 2, and 3 then Warning Msg No Data Found.

Also if the C8:CX13 is empty then exit sub.

4) The program looks for 5.1 in col K of the other worksheet, "RM Cost".

As explained above 5.1 is not the value to match.

5) Assuming it finds a matching value in col K, the program then displays the values that are in A:C of that row.

Assumes that if the program found all the 3 intersect values from "RM Purchase" to "RM Cost" then it has to show the MsgBox A:M

And how much do you know about writing a program in Basic? It won't be hard, but you have to at least be able to learn something about it.

I am a beginner and trying to learn the code.

In another way, each of the values in "RM Purchase" C8:CX13 are the sum of column E:J of "RM Cost"

So I want to show the Msgbox how the cost arrived from.

I hope the above information is useful to provide the code. Thank you so much for your attention and participation.

 

[BobBridges]

Ok, got it: If the user selects CU11 the program looks at rows 2 and 3 in the same column, and at col B in the same row, which gives three values. Then...what? It looks for those three values in cols A:C of "RM Cost"; if it doesn't find them it displays an error message, otherwise it displays A:M.

All that's easy enough to do, but what does your operator do then? Write down the information displayed? If someone has to write it down, wouldn't it be better to display it not in MsgBox but in a text file or in Excel itself, so the operator can cut and paste it? Surely that would be easier for the operator, and eliminate a lot of typographical errors.

One other question that we'll have to get to eventually: When the operator picks out a cell, CU11 or whatever, how will he indicate to Excel which cell he picked? I can think of three possibilities, and I'm sure there are others:

1) Write "cu11" in a cell somewhere on that worksheet where Excel is waiting for it.
2) Just leave the cursor there—the "selection", it's called technically, and then push a button on the worksheet.
3) Double-click on that cell.

I favor the second one, but any of them will work. Excel can be taught to look for a change in a cell (in the case of option 1), or watch for a double-click (option 3), but some people don't care for "active content", as that's called, because it opens the workbook up to security vulnerabilities. When it's necessary it works great, but in this case the button will work fine.

So next, about writing the program. How much do you already know? Can you write a simple VBA program? If not we'll have to start from scratch; if you know a little bit, I can let you start and then we can go over the program together and I'll show you what has to be added and how.

(Yes, I'm going to make you learn how.)

 

[Anbuselvam K]

Ok, got it: If the user selects CU11 the program looks at rows 2 and 3 in the same column, and at col B in the same row, which gives three values. Then...what? It looks for those three values in cols A:C of "RM Cost"; if it doesn't find them it displays an error message, otherwise it displays A:M.

Yes, you are correct.

All that's easy enough to do, but what does your operator do then? Write down the information displayed? If someone has to write it down, wouldn't it be better to display it not in MsgBox but in a text file or in Excel itself, so the operator can cut and paste it? Surely that would be easier for the operator, and eliminate a lot of typographical errors.

RM Cost is the details of each RM Purchase cell values C8:CX13. This sheet was prepared by me and frequently my company owner will visit and check the RM Purchase sheet. Note always RM Cost will be in hidden mode to avoid others opening and editing or deleting them. So to understand our company owner for the details of each cost I want to make the msg box.

One other question that we'll have to get to eventually: When the operator picks out a cell, CU11 or whatever, how will he indicate to Excel which cell he picked? I can think of three possibilities, and I'm sure there are others:

1) Write "cu11" in a cell somewhere on that worksheet where Excel is waiting for it.
2) Just leave the cursor there—the "selection", it's called technically, and then push a button on the worksheet.
3) Double-click on that cell.

I favor the second one, but any of them will work. Excel can be taught to look for a change in a cell (in the case of option 1), or watch for a double-click (option 3), but some people don't care for "active content", as that's called, because it opens the workbook up to security vulnerabilities. When it's necessary it works great, but in this case the button will work fine.

I decide to use the Worksheet_Selectionchange procedure to trigger the code when we select the range C8:CX13.
the selected cell should not be more than 1, also the selected cell should not be blank.

So next, about writing the program. How much do you already know? Can you write a simple VBA program? If not we'll have to start from scratch; if you know a little bit, I can let you start and then we can go over the program together and I'll show you what has to be added and how.

As I told you I am a beginner of the VBA Code. I tried but couldn't reach the target. see the below code which does not even reach 5%.

1) By using the below code I try to highlight a msg box which can show the row2, row3, and col2 values of the selected cell from the range("C8:CX13")

but the msg box shows "00" I don't know where the mistake happened in the code.


2) After getting the correct intersect value, then I will add the lookup procedure to search and find these values to another sheet

3) once found then show the A:M values in the Msg Box.

I know that the below-code level is lesser than the beginner level. with this level, I cannot reach the target soon.

That is why I posted and try to get the right code.

Private Sub Worksheet_SelectionChange(ByVal target As Range)

Dim row2 As Variant
Dim row3 As Variant
Dim col2 As Variant
Dim msg As Variant


Set target = Range("C8:CX13")
If target.Count > 1 And targetIsEmpty Then Exit Sub

row2 = Me.Cells(2, target.Column).Value
row3 = Me.Cells(3, target.Column).Value
col2 = Me.Cells(target.Row, 2).Value

MsgBox row2Value& & row3Value& & col2Value


End Sub

Also, when I select a blank cell from the range the code is still running and shows the Msg Box, as tsrgetisempty might be wrong in the code.

(Yes, I'm going to make you learn how.)

Great, I am with you!

 

[Anbuselvam K]

Also, If I select two cells then it will show the Msg Box. I think the code I wrote is entirely wrong or some important thing is missing.

Anyway, guide me on how to finish this code to learn further whenever your time permits.

 

[BobBridges]

RM Cost will be in hidden mode to avoid others opening and editing or deleting them. So to understand our company owner for the details of each cost I want to make the msg box.

Persisting on this question a little, I acknowledge that you want to keep RM Cost hidden. But you can still display the text in a way that lets the reader cut and paste. You can post the info in a different worksheet (not RM Cost). And I'm pretty sure that with a little work you can use InputBox for the same purpose.

...use the Worksheet_Selectionchange procedure to trigger the code when we select the range C8:CX13

I may be mistaken, but I don't think that's going to work. SelectionChange fires whenever you move the cursor, right? So if you mouse-click on a particular cell, that works alright. But if the operator uses the arrow keys to move the cursor (which he surely will do), that too will fire SelectionChange. You need one of the other ways.

I'll look at your code now and post more.

 

[BobBridges]

Ok, let's look at the code:

First, you're using Worksheet_SelectionChange; as I said above, I think that's going to give you a problem because every time the operator moves the cursor it'll run this program and you don't want that. (I know, you included that code before I pointed this out; I'm not complaining.) But let's go with that for now. Excel assigns to the variable "target" the cell (or cells) that are in the Selection, and then starts your routine.

The first thing you do is change the value of target to point to the entire range; therefore your routine no longer knows what cell your operator chose. We'll get rid of that statement.

Then you say "If target.Count > 1...". I'm not sure what it thinks target.Count is, but the first thing I think of is target.Cells.Count, the number of cells in the Selection. But since you've already changed target to C8:CX13, it seems to me that target.Cells.Count will always equal 570. What you really want to to exit the sub if the operator selected more cells than just one, right? Forget the AND statement; do this:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
  If target.Cells.Count > 1 Then Exit Sub

That'll not-do-anything if there's more than one cell in the Selection (and you're right that you want to Exit Sub in that case). Next you can check to see whether the target cell is empty, like this:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
  If target.Cells.Count > 1 Then Exit Sub
  If IsEmpty(target.Value) Then Exit Sub

You want to check not just target but target.Value because target has lots of other properties too: target.Font, target.Formula, target.Row and so on. It's the Value that you want to check. (It's true that Value is what Excel calls the "default" property of a cell, but I prefer to spell it out so there's no mistake.)

After that you collect the correct values from rows 2 and 3 and col 2 of the same worksheet. But in the MsgBox there's what looks to me like a syntax error: I think it should read like this:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
  If target.Cells.Count > 1 Then Exit Sub
  If IsEmpty(target.Value) Then Exit Sub

  row2 = Me.Cells(2, target.Column).Value
  row3 = Me.Cells(3, target.Column).Value
  col2 = Me.Cells(target.Row, 2).Value
  MsgBox row2 & row3 & col2

Row2, row3 and col2 don't have properties such as Value, you see; you assigned the cell Values to those three variables, which are "scalar", that is, they have only the one value and no other properties.

There's more to do with this program, but try that out and we'll go on from there.

 

[Anbuselvam K]

@BobBridges Thanks a lot for your time and efforts towards my post.

Your modified and suggested code perfectly shows the MsgBox row2 & row3 & Col2.

As we did not declare the code range, it triggers the code wherever the values are selected in the Sheet instead of C8:CX13.
We need to add the code to the specific range. Otherwise, as you suggested earlier we can add the code to trigger when a double click occurs.

As a second step, I tried matching the found values in other sheet columns 1, 2, and 3 to extract the specific cost details of the target cell.
I added some codes to get the found cell and found row data. but it showing "0" in the msg box.
I think For loop it is not properly completed or found cell value not assigned correctly.



Also, Added RM Purchase F12 cost details in the RM Cost sheet to check the different dates of data to find.

Private Sub worksheet_Selectionchange(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
If IsEmpty(target.Value) Then Exit Sub
Dim col2 As Variant
Dim row2 As Variant
Dim row3 As Variant
Dim LastRow As Long
Dim FoundCell As Range
Dim FoundRow As Long
col2 = Me.Cells(target.Row, 2).Value
row2 = Me.Cells(2, target.Column).Value
row3 = Me.Cells(3, target.Column).Value
LastRow = Sheets("RM Cost").Range("A7").End(xlDown).Row
For i = 8 To LastRow
If Cells(i, "A").Value = col2 Then
Cells(i, 1).Value = row2 And Cells(i, 2).Value = row3
FoundCell = Cells(i, "A").Value
FoundRow = Cells("A" & i & ":M" & i)
End If
Next i
MsgBox col2 & row2 & row3 & "Cost Details: " & vbCrLf & FoundRow
End Sub

Expecting Your further guidance.

 

[BobBridges]

I have to leave in about ten minutes so this'll be short; I'll take it up again when I get back this afternoon. But about your first point: Target is the variable that points to the cell the operator chose. To make sure that it's in correct range, you can use the Intersect method, like this:

If Intersect(Range("C8:CX13"), target).Cells.Count <> 1 Then Exit Sub

Intersect is a method of the Application object (and you can spell it out, "Application.Intersect", if you prefer). You feed the Intersect method two or more ranges, and it returns a Range that contains all the cells that are in all the ranges you included. In this case your program has already determined that target points to just one cell; this statement intersects target with C8:CX13. Then it counts the cells in the result; if it's 0, then what the user sent to the Worksheet_SelectionChange routine was not in that range and it should just ignore.

If you like to keep your statements shorter, just break it up into two pieces:

Set org = Application.Intersect(Range("C8:CX13"), target)
if org.Cells.Count <> 1 Then Exit Sub

 

[Marc L]

Hi Bob !​

​

According to how Excel badly manages the memory so your first unique codeline is the way to go​

as your second way forgets to free / release the object variable before the procedure terminates …​

​

 

[Anbuselvam K]

@BobBridges
I added both the line of code and got an error. see below the snapshot.

 

[rlv01]

I added both the line of code and got an error. see below the snapshot.

Maybe something like this.

Private Sub worksheet_Selectionchange(ByVal target As Range)
    Dim col2 As Variant
    Dim row2 As Variant
    Dim row3 As Variant
    Dim LastRow As Long, i As Long
    Dim FoundCell As Variant 'Range
    Dim FoundRow As Long

    If Application.Intersect(Range("C8:CX13"), target) Is Nothing Then Exit Sub
    If target.Cells.Count > 1 Then Exit Sub
    If IsEmpty(target.Value) Then Exit Sub

    col2 = Me.Cells(target.Row, 2).Value
    row2 = Me.Cells(2, target.Column).Value
    row3 = Me.Cells(3, target.Column).Value
    LastRow = Sheets("RM Cost").Range("A7").End(xlDown).Row
    For i = 8 To LastRow
        If Cells(i, "A").Value = col2 Then
            Cells(i, 1).Value = row2 And Cells(i, 2).Value = row3
            FoundCell = Cells(i, "A").Value
            FoundRow = Cells("A" & i & ":M" & i)
        End If
    Next i
    If FoundRow > 0 Then
        MsgBox col2 & row2 & row3 & vbCrLf & "Cost Details: " & FoundRow
    Else
        MsgBox col2 & row2 & row3 & vbCrLf & "Cost Details: No cost details were found."
    End If
End Sub

But you have bigger problems because there is something you ***MUST*** address first. This section of code:

    LastRow = Sheets("RM Cost").Range("A7").End(xlDown).Row
    For i = 8 To LastRow
        If Cells(i, "A").Value = col2 Then
            Cells(i, 1).Value = row2 And Cells(i, 2).Value = row3
            FoundCell = Cells(i, "A").Value
            FoundRow = Cells("A" & i & ":M" & i)
        End If
    Next i

implies that you want the loop to work on sheet RM Cost, but that is NOT what will happen.

When you write an unqualified reference like Cells(i, "A").Value = col2 it means that "cell" references a cell on the current sheet (RM Purchase) not sheet RM Cost. For that you need to fully qualify the reference, i.e. Sheets("RM Cost").Cells(i, "A").Value = col2

 

[Anbuselvam K]

@rlv01 Thanks for the code and the explanation of my mistakes.
I added your code and checked. it is showing "No cost details were found. " But the selected cells D13 intersect value has the cost details in RM Cost sheet row 8. See the snapshot below.






I need a msg box row 7 heading & Foundrow data A to M
A date to be DD-MMM-YYYY Format
B to D As per actual.
E to M Numbers with 3 decimals and the currency SAR before the number.

 

[BobBridges]

...forgets to free / release the object variable before the procedure terminates.

Does it really? All the time or just occasionally, if things go wrong? I supposed that Office is pretty good about such things. Maybe I should take up the Drop command. No, wait, "drop" is REXX; in VBA it's...what? Set obj = Nothing?

 

[BobBridges]

Ok, let's take a look at your code:

For i = 8 to LastRow
  If Cells(i, "A").Value = col2 Then
    Cells(i, 1).Value = row2 And Cells(i, 2).Value = row3
    FoundCell = Cells(i, "A").Value
    FoundRow = Cells("A" & i & ":M" & i)
    End If
  Next i
If FoundRow > 0 Then
  MsgBox 'with the cost details
Else
  MsgBox 'not-found error msg
  End if

I think we're going to have to talk about the first MsgBox, where you try to show the details of the FoundRow. But let's leave that for later; first we have to look at the way you're searching for the right row in RM Cost.

You say here "Cells(i, "A"). I never knew that was valid! I probably won't use it, but I thought it must be an error on you part. But no, I tested it, and it works.

The way you have it, you search through all the rows in RM Cost, and in every row where col A matches row2 you try to capture data from that row. Then after you exit the loop (having looked at every row), it tries to display the data from the last row it found where col A matches row2.

I know, you wanted to check all three values, not just the date. The first statement checks the date (only), and then does all the other stuff if the date matches. That next statement, the "And", doesn't at all do what you meant it to. It ... well, it does something complicated which I'll explain if you want, but anyway it isn't what you want.

What you wanted was something like this:

For i = 8 to LastRow
  If Cells(i, "A").Value = row2 And Cells(i, 2).Value = row2 And Cells(i, 3).Value = row3 Then
    ' blah, blah, blah
    End If
  Next i

I wouldn't do it that way, though. It'll run slightly faster and have less indentation if you do it this way:

For i = 8 to LastRow
  If Cells(i, "A").Value <> row2 Then GoTo IterateRow
  If Cells(i, 2).Value <> row2 Then GoTo IterateRow
  If Cells(i, 3).Value = row3 Then Then GoTo IterateRow
  ' blah, blah, blah
IterateRow
  Next i

Coding style is entirely up to you, though. My way runs faster, but unless you have scores of rows to search your operator will never notice the delay.

Now, a question: Is there any chance that more than one row in RM Cost will match all three values? If so, what do you want to do in such a case?

If all the rows are unique in that way, then don't forget to stop looking once you've found the row you want. Why make the program do any more once you have what you want?

For i = 8 to LastRow
  If Cells(i, "A").Value = row2 And Cells(i, 2).Value = row2 And Cells(i, 3).Value = row3 Then
    ' blah, blah, blah
    Exit For 'we're done; jump outside the loop
    End If
  Next i

Once you have all this under your belt and have adjusted your program, we'll talk about collecting and displaying the information from FoundRow in RM Cost.

 

[rlv01]

I need a msg box row 7 heading & Foundrow data A to M
A date to be DD-MMM-YYYY Format
B to D As per actual.
E to M Numbers with 3 decimals and the currency SAR before the number.

A guess.

Private Sub worksheet_Selectionchange(ByVal target As Range)
    Dim col2 As Variant
    Dim row2 As Variant
    Dim row3 As Variant
    Dim LastRow As Long, i As Long
    Dim FoundRow As Range, FoundCell As Range
    Dim FoundRowNum As Long
    Dim Msg As String, CostD As String

    If Application.Intersect(Range("C8:CX13"), target) Is Nothing Then Exit Sub
    If target.Cells.Count > 1 Then Exit Sub
    If IsEmpty(target.Value) Then Exit Sub

    col2 = Me.Cells(target.Row, 2).Value
    row2 = Me.Cells(2, target.Column).Value
    row3 = Me.Cells(3, target.Column).Value
    Msg = "Date: " & Format(col2, "dd-mmm-yyyy") & vbCr & "RM Code: " & row2 & vbCr & "Real Name: " & row3 & vbCr

    LastRow = Sheets("RM Cost").Range("A7").End(xlDown).Row

    With Sheets("RM Cost")
        For i = 8 To LastRow
            If .Cells(i, "A").Value = col2 And .Cells(i, "B").Value = row2 Then
                FoundRowNum = i
                Set FoundRow = .Range(.Cells(i, 1), .Cells(i, .Columns.Count).End(xlToLeft))
                For Each FoundCell In FoundRow
                    CostD = CostD & .Cells(7, FoundCell.Column).Value & ": "
                    CostD = CostD & FoundCell.Text & vbCr
                Next FoundCell
            End If
        Next i
    End With

    If FoundRowNum > 0 Then
        MsgBox Msg & vbCrLf & "Cost Details: " & vbCr & CostD
    Else
        MsgBox Msg & vbCrLf & "Cost Details: No cost details were found."
    End If
End Sub

 

[Marc L]

Set obj = Nothing?

Yes !​

​

 

[rlv01]

Yes !​

​

Why do you say so? I've always understood that setting an object to nothing is not needed for standard Excel objects, except in a few special circumstances. The object resources are released when the Sub ends.

So for

Sub Test()
    Dim WB As Workbook

    Set WB = ActiveWorkbook

    ' code here

    Set WB = Nothing
End Sub

The Set WB = Nothing is unneeded.

 

[Anbuselvam K]

Now, a question: Is there any chance that more than one row in RM Cost will match all three values? If so, what do you want to do in such a case?

Only the date will be more than one row not all the three values.

 

[Anbuselvam K]

A guess.
Code:

@rlv01 The code is working perfectly. thanks a lot!

 

[Marc L]

Why do you say so? I've always understood that setting an object to nothing is not needed for standard Excel objects, except in a few special circumstances.

As Excel badly manages memory the only way to be sure the memory to be free is to close Excel !​

The reason why it's better to set any object variable to Nothing before the procedure ends.​

Most of the time this is not a concern to not free objects variables.​

​

But in some cases that can lead to a crash and as Excel is very not a database software so data can be lost …​

The memory issue may come with people opening Excel the morning and just closing it at the end of the day.​

Just closing Excel at break lunch may avoid a crash but not always as the more procedures launched without releasing objects variables,​

the more chances to have such crash. Ok it's rare but can occur.​

​

The first time I met the case was in a specific women team office, hearing often « oh no again Windows crashes ! »​

but in fact the culprit was under VBA only.​

I had to update a procedure for an user but when I saw the original code, so uggly, I prefered to ask first to the user​

what it is supposed to do and what she needed to be added. I replaced the original ~350 codelines to a new procedure​

with less than 50 codelines and as I use to avoid object variable when that's useless just using With block for example.​

And for both objects variables I used they were set to Nothing before the end.​

No more than two weeks Iater I was summoned to the head of department where I was asked for what cheat I used.​

So surprised without understanding the subject ! In fact the other women of the team still had a crash every day​

but no more the woman for which I wrote the new code so, as I had no idea why, I told the boss to just compare both codes.​

He asked to other VBA coders to investigate and their conclusion was original code objects variables were not set to Nothing.​

The report omitted my code was better, smaller, easier to maintain, faster, safer, …​

Then each time I met a 'no obvious reason why VBA crashes Excel or worse Windows' case​

often just setting objects variables to Nothing before the end vanished the issue.​

​

 

[Marc L]

Sub Test()
    Dim WB As Workbook

    Set WB = ActiveWorkbook

    ' code here

    Set WB = Nothing
End Sub

Here Set WB = ActiveWorkbook is the common useless case whatever the VBA forum !​

Just work directly with ActiveWorkbook rather than creating a duplicate variable …​

​

 

[BobBridges]

I never use ActiveWorkbook; I can never be sure that some enterprising user didn't run the macro while looking at a different workbook (if he has more than one open at the time). I use ThisWorkbook a lot, but for other workbooks I set a variable to point to it by name, eg "Set owb = Workbooks("MyBook.xlsx").

 

[BobBridges]

Most of the time this is not a concern to not free objects variables. / But in some cases that can lead to a crash and as Excel is very not a database software so data can be lost …

I don't recall Excel ever going bad on me during normal operation, even when I'm running a lot of VBA routines. But I do a lot of programming, and if I code for more than an hour or two at a time, yeah, sometimes Excel starts acting up. Sometimes it crashes, sometimes it throws out an error message that isn't correct, sometimes it doesn't process a statement correctly ... different things. Usually I close Excel, restart it and it's back to normal. I'll think more about setting things to Empty now, especially large arrays.

Objects, though, I wouldn't think would be a memory problem; an object is just an address, isn't it? I mean, if I say "Set owb = ActiveWorkbook", doesn't it just create a variable and then have it point to the object that already exists elsewhere in RAM?