Having trouble with ActiveX Textboxes

Hi All,

First time poster. I need assistance with a "Product Viewer" sheet I'm currently working on. I am new to the VBA side of things.

I'll provide some background. I have over 600 products. Each product in our library has approx. 100 columns of data. All of this data is pulled from MULTIPLE sheets. These columns of data house product specs like Interest Rates and Notation information. The issue here is it's difficult to read the Notation information in it's current state (standard sheet view - it's often 800+ characters per cell). Additionally, trying to edit this information is just plain painful.

So I have created a sheet in hopes of creating a place where you can visually see all of the information for that product, in one place and be able to edit this information.

Here's what I've done so far: (I am in no way wedded to any of these methods, it is simply all I could manage, if you have a suggestion for a better "search" method or the like, please advise)

I have used the INDEX / MATCH functions to find the ROW, and then manually specified the column to return the value. I have removed sensitive information from the below formulas.

The search Formula (i'll call this the H formula because these all sit in the H column)

Please Login or Register  to view this content.

Cell B10 is linked with a combobox that uses "google style" searching. Used guide from Trumpexcel - excel-drop-down-list-with-search-suggestions

Please Login or Register  to view this content.

Column "K" is the product name column

Please Login or Register  to view this content.

Column "55" is the field that I wish to retrieve. These columns contain TEXT notation information and are NOT numerical.

All of these formulas sit in cells H1, H2 etc. The formula is repeated but with different column numbers to retrieve different information from the same row of product matched.

E.g.

Please Login or Register  to view this content.

Now to display the information found:
The cells in the "H" range are linked to the ActiveX Textboxes using their "Linked Cell" property. Here's where i run into issues. If a user edits the textbox, it then removes the formula and replaces it with just text (in the H cell). I am trying to find a way to have these textboxes to be dynamically linked to the other sheet based off the search results. Is there a way to have the "Linked cell" property update whenever a search is made?

Currently the combobox is live, and as soon as a selection is made -> B10 is updated -> and therefore INDEX / MATCH formulas kick in (H range cells). Once a search is made, and a product selected, the TEXT values are displayed in the corresponding textboxes. Approx. 8 Textboxes currently inuse (although planned for more).


I have some ideas how I could do this but have a hit a wall on where to start.

Some of my ideas below.

Option 1:
Create an "edit" command button specifically for each textbox, that modifies the "LinkedCell" property to the MATCHED cell from our INDEX / MATCH search function.

Then create a "save" button that reverts the textbox's "LinkedCell" property back to the "H" formulas.

This would be useful for one-by-one edits, and probably the simplest coding (I thought).

I created the buttons in the screenshot, and then brain farted on how to do it.

Option 2:
Somehow create an array or string thing (not very good with VBA) so that whenever i click a singular "edit" button, all textboxes "LinkedCell" properties are modified, according to the column number specified from our "H" formulas.

So I would search a product, see the information in the 8 textboxes, then hit the global edit button, this would change all textboxes LinkedCell properties to the ACTUAL cell. Then hit the global save button which would revert the LinkedCell properties back to the H formulas for viewing.

Option 3: (would prefer this option to be honest)
Stop the combobox from being a live search, and create a "search button" so when the user hits search, it finds the product using our H formula, and uses this information to update the LinkedCell properties immediately. So the user could make live changes without using any "edit" or "save" buttons.

Sorry for the wordy post, i hope i was clear. Please advise should you require further information.

See attached for layout screenshot.

Please any advice here on any of the above is going to help. I. am. stuck. :D

Thanks guys.

Thanks Keebellah. Wasn't too sure how much information you would need. It will take some time to remove sensitive information from the whole database. I will reply when I have the requested dummy file ready.

Hi Keebellah,

Please see attached Dummy Data. I have removed almost everything, and left 8 test products and confirmed the combobox still works.

Just looking for a way to use the "Product Viewer" tab as a means to input data directly into the MAD sheet tab using ActiveX textboxes. If you can think of a better way this goal could be achieved, I am definitely interested. I am in no way wedded to the current layout, method, code or anything. Just trying to create a place thcan be used for edits/viewing.

Regards,

Max

Why not use the value attribute rather than the linked cell, that way they're disconnected from the cells - they can't overwrite them then.

You'd be better off with a userform too as you don't really want to use ActiveX controls on worksheets at all. They're incredibly unstable have a look here https://www.google.co.uk/webhp?sourc...eet%20controls and by using them, you're asking for trouble.

Thanks for taking a look guys. So when you say Userform, are you referring to the buttons just above the ActiveX buttons? I thought it had to be activeX to achieve what i wanted? If there's a better way, definitely open to it.

Not sure about the enter key thing. I remember i was doing a "password" entry for "editing" but could never get the edit function to work. So a lot of trash code in there for things i tried and stopped halfway.

When using the Value property, how do i get that property to send to the MAD sheet? Like a "save" button that sends the value across?

Please Login or Register  to view this content.

do you mean something like the above? real novice here guys. Only just starting using excel a few months ago.

Thank you for the helpful link! That guide is helping me understand the VBA side of things. Definitely seems like userform will be the better option. I'll start familiarising myself with the userform.

No worries Keebellah. Appreciate your input.

Hi Keebellah,

I do apologies, I hadn't realised how much was required to achieve my goal. I can see you created some textboxes in the user form. Its the combo box im having trouble with. Trying to get it to operate like the one I already have is proving difficult. I just don't understand the VBA side of things to code the tables into it.

Thanks for you time and appreciate the effort.

Max

That's ok Keebellah. I'll come back to it after some more research. It seems this task is out of my depth.