Posts by mbkupfer

Hi guys,

Wanted to see if anyone knew why this issue is occurring. Here is how it can be replicated in the attached workbook: 1) Filter the States column for one state 2) Change the combobox metric selector to something like "Temporary penetration rate" 3) Clear filter and you will notice that other rows still have old formula referencing the previous metric (assuming this is the first time you open the workbook, the older cells will still reference "Total employment")

I tried my best to replicate this in isolation, while also maintaining a similar environment that created this issue.

I'm also pretty certain this isn't a defect with Autofilter since I did a similar experiment and couldn't create this issue.

Thanks so much for you help!

Thanks, that works for me. The error handling is a bit advanced, but what you helped me most with is the "#" usage in the front of the sheet name! Thanks for helping

Sounds like a very vague question, but I attached an excel file to help illustrate. Basically what I'm trying to do is make an outline that links someone to a row on another sheet. I was able to make it manually (Column H), but those do not dynamically change if you add another row on the "Destination" tab. Does someone have a solution?

I was thinking of using the

=Hyperlink

formula, perhaps that could be one way to do it, but I kept getting an error when trying to add the row number.

As always, thanks for taking time to help!
Maxim

Yes, I caught that later after my post--was driving me crazy! I did a 4 digit rounding just to get the percentage accurate to two decimal places, but still same idea.

Thanks for responding!

If you look at my attached worksheet, you will see that the decimal -0.26 in row 10 and row 17 are both equal, yet the are ranked differently. Does anyone have any insight as to why this is the case and what would be a better way to rank.

Thanks!

Brilliant solution! I assume it doesn't alphabetize though, does it?

I would like to dynamically rank a list of changing values. I have attached an excel worksheet that has a hypothetical example as well as my current work-around. The problem with my work-around is that it works using a rand() number as a tie-breaker, but the ideal method should actually use an alphabetical sort when there is a conflict. Besides being more logical, an added benefit is that the ranks will not be volatile as there is a fixed tie-breaker and not a constantly changing random variable.

Do anyone know if there is a way to do this using the more ideal method?

Thanks!

Re: Run-time error '438' occurring for someone using a workbook I shared

This particular user does not.

Hi all,

I unfortunately cannot share the workbook in question as it has proprietary company information on it. That being said, I wanted to know if anyone had an idea on what could be the cause of this error. The worksheet works perfectly on my computer, as well as others in my office, yet I have a user that is saying that is getting this run-time error as soon as they open it. How could I trouble shoot this if I can't even recreate the error on my end?

Thanks for the help!

Re: Mathematical algorithm to get an inverse of an RBG color

Quote from bob22;796814

Why not use something like:

Code

[COLOR=#666666][FONT=&quot]Range("A1").Interior.Color = RGB(255, 0, 0)[/FONT][/COLOR]

where the color code could vary depending on the value of cell's content.
Conditional formatting would work and a vba could also do it.

I'm trying to create a heat map that responds to negative values by turning red, yet still keeping the same magnitude (i.e shade).

There is an excellent map chart tool, but it is not functioning on other versions of excel. Therefore, I'm trying to build something similar from ground up. I did come up with a solution though. PM me if you are interested.

Re: Mathematical algorithm to get an inverse of an RBG color

Sure.

If you check my attached spreadsheet, I have created 3 shades of colors and supplied their respective RGB and fill codes.

How do I convert the decimal form of one blue to it's corresponding red? I feel like there should be a constant but there is no constant difference as this is a little more complex.

forum.ozgrid.com/index.php?attachment/73257/

I also believe the link you sent me is what I want. Do you know the formula to invert?

Hi all,

I'm trying to create a thematic map which can adjust for negative values as well as all positive values. For instance, positive, would be lighter shades of blue to darker shades. For the values that can be negative, such as a change/delta, then I would like to incorporate red shades.

So lets say my darkest shade of blue is RGB(0,0,255) and for each lighter shade I simply up the R and G by a constant. Now what I want to do is invert it so that I can get the same intensity, so to speak, but on the other end of the spectrum. There must be a simple mathematical conversion, but I'm scratching my head with this one. Any help would be much appreciated!

Re: Workbook keeps opening to the wrong scrollrow

Anyone have an idea? Should I rephrase my question?

When opening the workbook, the "menu" tab should not scroll to a row below, but it keeps doing that.

I'm almost certain that the below block of code is causing the issue. What is stranger is that when creating another workbook, I couldn't replicate this error. I would be open to workarounds, but I like the format I'm working with.

Thanks guys!

Private Sub ComboBox1_Change()
     
    ActiveWindow.ScrollRow = Worksheets("control.fullDataSet").Range("fullDataSet.jumpToRow")




End Sub

forum.ozgrid.com/index.php?attachment/72723/

Re: How to press other buttons while a spin button is active

Quote from Infomage;793884

Can you use a named range for the dropdown source, and adjust that when the range of values changes?

Didn't realize that was possible, thanks!

Re: How to press other buttons while a spin button is active

Quote from rory;793868

If you're trying to avoid ActiveX why have you used an ActiveX spinbutton rather than a Form one?

ActiveX allows me the customization I need to change the max value dynamically. If this spin button is not compatible, it won't be the end of the world, but the drop down needs to be.

Re: How to press other buttons while a spin button is active

Quote from Infomage;793826

As an alternative, can you use shapes with macros for everything, instead of the form controls?

I suppose so, but I don't want to reinvent the wheel so to speak. Thank you for advice.

Re: How to press other buttons while a spin button is active

Quote from Infomage;793736

I don't know what you're trying to achieve ultimately, but a quick experiment reveals that if you use a form button instead of a shape then the focus is dropped from the spin button... Can you assign a macro to a button instead of using a hyperlinked shape?

Brilliant suggestion, works for me. Just out of curiosity, could you explain to me why the behavior is different? Reason I prefer shapes over buttons is because buttons are very plain and can't be designed, unless you use activeX but I try to avoid that due to incompatibility with previous excel versions.

In regards to your question: my purpose is to improve UX since it seems as if the menu button is broken when in fact the spin button is just activated.

Thank you for your kind assistance!

Re: How to press other buttons while a spin button is active

Quote from Logit;793686

Post your workbook for review. Do not include confidential information.

Here is my attached workbook that simulates my issue. Basically, the problem is that if the spin button is activated, the "menu" button cannot be clicked unless you click somewhere else in the sheet.

Thanks for the help. forum.ozgrid.com/index.php?attachment/72700/

I have a dashboard with a spin button. it works fine, but when I'm navigating using the spin button I can't press another button that will move me to another worksheet unless I explicitly click out of the spin button. I have attached a picture to show what I'm talking about. The spin button on top is currently activated. I can't click "menu" button until the spin is un-activated. What would be a solution where I wouldn't have an extra step of clicking outside the spin button to then click menu.

Thanks!

[ATTACH=CONFIG]72689[/ATTACH]