Thread: Excel VBA - Merged cells

Only just saw your post.

If you are trying to compare the contents of the cells that the user has selected to the contents of cell "D3" then this will work

If Selection.Cells(1, 1) = Range("D3") Then
Debug.Print "Match"
Else
Debug.Print "No match"
End If

The contents of merged cells are stored in the 1st cell. If the selected cell is not merged then the .cells(1,1) is redundant but does no harm.

Paul,

I tried your solution with no luck. After some more testing I've found that the Type Mismatch error occurs if the active selection is a range, regardless of whether that range is referenced in the code or not. Any ideas?

Here's my code....Range K3 is the uppper LH cells of a merge. All the others are single cells.

Sub Help_Dialogue()
If Selection = Range("D3") Then
MsgBox "User entry required. The PM's estimate which is based on the original sales estimate. Enter the contract value and costs in this column."
ElseIf Selection = Range("F3") Then
MsgBox "Calculated data. This column summarises Approved Variations which are entered in the Variations tab."
ElseIf Selection = Range("G3") Then
MsgBox "Calculated data. The Current Estimate is the sum of the PM's estimate and Approved variations."
ElseIf Selection = Range("I3") Then
MsgBox "Calculated data. Summary of the invoicing and costs to the end of the current month, derived from the Labour Forecasting, Claims and Purchasing sections below row 18."
ElseIf Selection = Range("J3") Then
MsgBox "Calculated data. Summary of the invoicing and costs to come from next month, derived from the Labour Forecasting, Claims and Purchasing sections below row 18."
ElseIf Selection = Range("k3") Then
MsgBox "Comparison of the orginal sales estimate to the current estimate. Enter the Sales estimate CV, cost and point count. All other fields are calculated"
Else: MsgBox "No help available. Please ensure you have selected a column, row or section title cell"
End If
Unload fmHelp
End Sub

Mark,

I can see what you are trying to do. I assume that the user selects the cell that they want some help with and then hit a help button. You then give a context sensitive description back about the selected cell. So you don't care about the content of the cell, which my suggestion above was geared to.

Can't look at right now, have to get to a shop at Stafford by 5pm.

Will have a look at tonight after dinner but I suspect you will need to use something like I have below. But not tested this with merged cells.

While the other lines of code might not give an error they will not always work (I think) because the default property is ".value" and you might get a false match.

Should use:
If selection.address = "$D$3" Then
probably wont work for merged cells but might if you have "$K$3:$K$4" (for example)

I would actually give each cell a reference name and then use
if selection.address = range("PM_Estimate").address then
This really should not care if the cells are merged or not.

This way you can move the cells around and you code will still work. The way you are doing it will require recoding if you restructure your spreadsheet.

Also I would use a Select Case statement as it is easier to add and remove from. I will send you something later but maybe this is enough info for you to fix it.

Cheers

Mark,

Try:

Sub New_Help_Dialogue()
Dim l_Message As String


Select Case Selection.Cells(1, 1).Address
Case Range("PM_ESTIMATE").Address
l_Message = "User entry required. The PM's estimate which is based on the original sales estimate. Enter the contract value and costs in this column."
Case Range("APPROVED_VARIATIONS").Address
l_Message = "Calculated data. This column summarises Approved Variations which are entered in the Variations tab."
Case Range("CURRENT_ESTIMATE").Address
l_Message = "Calculated data. The Current Estimate is the sum of the PM's estimate and Approved variations."
Case Range("CURRENT_MONTH").Address
l_Message = "Calculated data. Summary of the invoicing and costs to the end of the current month, derived from the Labour Forecasting, Claims and Purchasing sections below row 18."
Case Range("NEXT_MONTH").Address
l_Message = "Calculated data. Summary of the invoicing and costs to come from next month, derived from the Labour Forecasting, Claims and Purchasing sections below row 18."
Case Range("COMPARISON").Address
l_Message = "Comparison of the orginal sales estimate to the current estimate. Enter the Sales estimate CV, cost and point count. All other fields are calculated"
Case Else
l_Message = "No help available. Please ensure you have selected a column, row or section title cell"
End Select

MsgBox l_Message, vbInformation, "Purpose of cell."

End Sub

You can replace the select case lines with nested IF THEN ElSEs but I prefer the SELECT CASE.

You will see that I assign the message to a variable and then display the variable at the end. This is also a personal preference and is only to separate the decision making from the displaying. Makes it easier to change one later without affecting the other. Overkill for this situation but 20 year habits are hard to break.

I have attached my test sheet as a 2003 xls file. Hopefully it didn't lose anything in the conversion backwards. If it doesn't work PM me your email address and I will send you the 2007 file.

Note that you may achieve what you are after by inserting comments against the actual cell. See the red triangle in the top right corner of the cells. Hover mouse over cell and the comment will appear. To add a comment right-click on a cell and select "Insert Comment".

Interesting Post....

I would just use a Validation, but your code works. Validation looks cleaner rather than a comment. Also if you write the validation in VBA, it prevents the user right clicking a removing the comment.

Its great to see VBA on a forum like this... I do a fair amount in my job, and I like to see how others write code to there style.

Dorko

Interesting point, I've not used the validation property before. I don't have much need for validation as I rarely use Excel as an entry point. I like to build custom forms and often the only role of excel is to store the data. I am allergic to Access. These days I am much more likely to use VB.net.

Yes, I have had a bit of a play and agree that is a better way than inserting a comment. I went looking for a tooltip option which I think was an option in earlier versions of Excel and this explains why I could not find it, cause it is now part of something bigger and better. I should have googled.

You can then even define what type of data (eg Decimal) and put some sanity limits in case someone enters something stupid.

I also agree that sticking them in the code is a good idea but if you want to enter them via Excel the option is under the DATA menu.


I did want to answer the original question just in case Mark is it was, after all, the original question.

And then I thought that I would use some good programming practices to restructure that code.

Thanks Paul.

Your Select Case code works fine and is much neater than my if..else. Better still, in applying it I found the cause of the original type mismatch error. It was my use of "Selection". Once I changed this to say "ActiveCell.Address = Range("F3").Address...." it worked fine. But I'll adopt the select case code anyway.

You're right in your assessment of what I'm doing. Its selection-sensitive help code. The spreadsheet is complex, aimed at low level users, with my task being to dumb down an existing project cost/forecast tool to something more user friendly. The use of "Comments" would seem simple enough but was rejected as being not obvious enough!

Dorko, thanks for your comments as well. I often use data validation but from the tools menu, not in VBA code as yet.

Cheers guys and thanks again.

I just reread my last post and I have to apologise for my sentence construction at one point.

Happy to answer any VBA questions given my lack of Land Rover knowledge