Display shape data in a text box?

hi

Basically i have a cell linked to a shape which shows the count of the particular item within the shape, i also have a macro running to colour the shape according to the value range. Now what i need to do finally is to be able to click on the shape and get the description of the latest fault on that particular cell. I should probably explain this a bit more, i have a database with different faults on different locations, i then have a pivot table to get the total count of faults on each location and then finally i have the shapes linked to the cells showing the count of faults with conditional formatting.

Having searched on the web, i found that we can actually have a text box appear once a cell is selected (only a data validation cell), this text box shows the input message that goes out to a user entering information in the cell.

My idea is somewhere around the same lines, hence i think it is possible just dont know how to do that.

If you need any more information just ask me n ill try to explain it a little bit more.

Thanks

Hello abz,

To provide you with an expedient answer, you should post your workbook. This will serve as a guide when writing the code to the formats, fonts, formulae, etc. If you have confidential information, you should create an expurgated copy for posting.

Hi Abz;

I'm not positive that I can help, but I'll try.
1) When would you like the macro that finds the latest fault to trigger?
2) How would the macro determine what the latest fault is? I see no dates in the table.

Firstly the macro will have to trigger when any of the shapes is clicked on, or selected by the user, so the shape the user clicks on is already linked to the cell with the number of faults, just need to display the lates fault in a separate textbox or a square/rectangle shape.

I don't use Excel 2007, but at least I can write the framework for what you want. Specifically, I'm not sure what you mean by showing the latest fault in a textbox. I should be able to put it into a shape (I hope - never tried it before, but at least I understand what you mean).
Quite honestly I would do it differently. I would just have the latest fault in the cell right next to the shape. It would get updated automatically when a later fault was entered in sheet4 table. The user wouldn't have to click anything to see it.

Once again thanks a lot and i will be waiting for your reply, let me know if you need more info.

It may take a couple of days. It's 4 in the morning (and I have got to get to bed), and I will be gone most of tomorrow. I should be able to get to it tomorrow night though.

1 question. I notice that the workbook you uploaded doesn't have any macros in it. Do you know how to edit macros? If not, I will write it differently.

Hi abz;
Sorry this has taken so long, we had a power failure here. We just got our power back. I was gone all day yesterday which is the only thing that kept me from going out of my mind.
I happened to be just finishing my reply to you when the power went out, so now I have to start again.

That is something i suggested earlier that i would divide this task into 2 main tasks, first one the cell next to the location name would pick up the lastest fault from sheet 4 and display it, and the second bit would be that the text box picks up the cell and then only appears when the user clicks on the corresponding location shape.

So are you saying that you would like both? I'm working on a formula that will put it into the cell you want.

The problem of not having the text box is that the shape are going to be on a geographical map rather than being next to a cell where the latest fault is being displayed. I hope you understand what i am saying.

I have no idea what this means. I hope you mean that you want to move the shapes to another sheet that has a map on it. If not, then I have no idea whether you can move a shape with a text box attached.

I can give you a lead on a text box appearing and disappearing, i found this on the following link:

Thanks for the link. It puts another tool in my toolbox. They are pretty easy to do, so no problem there.

Just to mention i am using a code which is a worksheet change code and the button is merely just to go to a cell and edit it which in turn triggers the worksheet change code.

I cleaned up your code. You want to put the code for updating colors in a separate macro, so you can call it directly without having to artificially change something to get it done. Also, if you want to move the shapes to a different sheet, it could easily make it easier later on.

Please Login or Register  to view this content.

If I may make a suggestion:
You can get rid of the button altogether, and have the shapes automatically updated by using this

Please Login or Register  to view this content.

That way the coloring gets updated whenever any cell changes on the sheet. (Delete CommandButton1_Click, and you can take it out of Worksheet_Change).

I'm working right now on having the shape click put a text box next to it.

Hi abz;

I've been struggling to find a formula that would extract the latest fault. I even asked for help. I just can't do it in 1 formula to my satisfaction. It needs an additional column.

These are the 3 best solutions.
1) Include a column for "Date of Last Fault" (my recommendation).
2) Have a macro fill in "Latest Fault" when ever anything changes.
3) Add a column to the list of faults. It would be a formula (=$A1&$F1) and could be added automatically by a macro.

The shapes will have their own macro, like you have now, that just reads the information from the sheet & puts it into the textbox. Hopefully that way the shape can be moved anywhere you want it.

I'm on my way out the door right now, but I'll be back in a few hours and finish this.

Hi Abz;

I'm just now getting to your problem.
Looks like you don't really need me. You're getting where you need to without any input from me.
Would you like me to work on it, or just answer questions?

These are the things I was planning on setting up.
1) Coloring will be automatic. No need for a button to update them.
2) Leave the button for changing the color scheme with a few changes.
2 - A) Load the current values into the form.
2 - B) Allow the user to select their own colors. (This would require saving the colors somewhers).
3) Put the Date of Last Fault and Last Fault next to the Location on Sheet 1
4) Make the shapes as portable as possible so they can be moved to any sheet. (This will have issues that have to resolved for the Button to find them, but I don't anticipate anything particularly difficult).
5) Could consider allowing the user to select where the Textbox is displayed, but this could be overkill.

Hi Abz;

I personally believe that there should never be out dated information on a sheet. I believe that everything that gives the user information should be updated automatically. They should not have to click any buttons to see the most current information.

If for some reason it is impractical to update the info immediately, then there should be a date and/or time display of when the info was updated.

I don't believe that a program is user friendly if the info is not updated instantly or have a date/time attached to it.

1) Coloring will be automatic. No need for a button to update them.

I thinking leaving the button would be a better option, gives the user an option to enter his own range for the colours.

You misunderstood me. I haven't looked at your pivot table or any other data on the workbook, but I assume that the values in C8:C13 can change. I presume that you would want the shapes to change color automatically, not have to click a button to get them to update.
Having a button to set the range for the different colors is not a bad idea (not how I would do it, but everyone has their own preferences), but I would make the user click the button only to change the range, not update the colors. That should be automatic.

2) Leave the button for changing the color scheme with a few changes.
2 - A) Load the current values into the form.

This is what my worksheet is currently doing, a formula is being used to extract the values from the pivot table and then the userform allows the user to select their own range.

To have the workbook more user friendly, the range should be stored somewhere and have the from-to range in RngSelect filled in with the current values.
Storing the values somewhere is how the colors could be updated automatically when C8:C13 change

I am getting the last fault on this sheet through a macro at the moment, i guess i can trigger that macro with the command button, just makes it quicker, i think to get the latest fault i will have to do the same macro and then paste it in the column next to the fault and then concatenate the 2 columns.

I wouldn't concatenate them. I would leave them in separate columns. I would also have sheet4 automatically sort itself anytime it changes. Then it would be fairly simple to have the cells with the Latest Fault (date & description) update automatically. No macro to run.

But do you have a solution to the problem that the txtbox currently does not pickup the location name to which the fault is related?

I must have missed something. It looks like you already have the Location in it.

Make the shapes as portable as possible so they can be moved to any sheet.

This is not required as i am going to have the shapes on the same sheet and is not really beneficial. But just out of interest how would you do that?

I thought you were going to move them onto some kind of map. Just moving all the macros for the ovals into their own module and making sure they did not refer to ActiveSheet might have been enough. The data that determines their color is on a specific sheet which may not be the ActiveSheet.

In the attached file, I have changed the name of the module holding the oval subs, and added a sub that opens the Textbox only when an oval is clicked. I think I would consider having the Textbox disappear when the user clicks the shape again rather than when they click an empty cell. That way they can determine for themselves when to get rid of it.
I also changed the Sheet1 Worksheet_SelectionChange, so that clicking into an empty cell hides the Textbox. I also put a few notes inside it, to give you a couple of pointers on good programming.

Hi Abz;

A few questions, what is the purpose of having 2 similar codes, 1 is in the module 'Oval' and the other is in sheet 1, arent they both doing kind of the same thing?, could you kindly explain them a bit more as to what functions they are doing separately, im not a vba expert. I mean i tried deleting the code in sheet 1 and then the txtbox would appear but it wouldn't disappear so i am guessing its got something to do with that.

I left your Worksheet_SelectionChange more or less intact so you could see my notes next to your code. I changed it so that when the user clicked on an empty cell it would make the text box disappear (which is what you had also).
If you look at it again, you will see that I have a line "If False Then", and down further "End If 'False". The code inside that section will never execute because False is never True. That was your code with my notes. Once you understand them you can delete everything from "If False Then" through "End If 'False".
I mentioned that my preference would have been to have the shape toggle the Textbox on and off, but I didn't want to do it unless that's what you wanted.

Is it possible to have another text box (this one does not need to disappear or appear when anything is clicked on, just needs to be constantly displayed in the sheet somewhere) to display the name of the location and its fault count if it is red, i.e. greater than the specified value entered by the user?

Which location would be in the Textbox if there are 2 that are red? Where would the value entered by the user be stored? Or are you planning on having it look at the color of each shape looking for a red one? Or are you going to have the user click another button?

Since you have already designed one Textbox, you should be able to design this one also. Give me a better idea of what you want inside it and when and/or if it will ever change, and I'll help you figure out where to put the code.

Hi Abz;

Sure it can be done. I don't know what you need me for. You can already do all this. The only question is where to put the code, and how and when do you want to update it.

If you are going to keep the button to update the colors, I would put it in the same macro that is assigned to the button. No point in having 2 buttons. And you wouldn't want the shape colors and the new Textbox to get out of sync with each other.

Another suggestion. Do Not use range addresses in your macros. Only use names.
For example:
In Search() you have

Please Login or Register  to view this content.

Don't do it that way. Name the range G8:G13 something like Fault_List then:

Please Login or Register  to view this content.

Then later on when you decide to change the design of the sheet (and I guarantee that you will), you can move Range("Fault_List") any where on the sheet, and you don't have to change any addresses in the macros.

Do the same thing for all addresses in your macros.

An added benefit is that it makes it easier to debug your macros. You won't have to keep switching back and forth between the VBE and Excel to make sure you are working on the correct range.

By doing this to the range A22:A24 you can then move that range out of sight and the macros will still know where to find it.

I'll be waiting to hear how you did.

Hi Abz;

Here's your file.

I put a lot of notes in Worksheet_Change so you can learn how to do this yourself.

Hi Abz;

You're welcome. I will keep this thread in my subscriptions for at least a month. If something else comes up, just add another message and I'll see it.

Don't forget to mark the thread solved and rate the answer if you liked it.