Looping an Excel Inputbox with Single Range

Hello my potential saviour,

I have been driven to despair with what I thought would be a simple VBA loop but after countless hours and many different searches I'm at a loss so have turned to you to ask for help.

Although I have found similar people with my issue, none of them have the exact same problems, so I thought I'd start a new topic and lay out the steps of what I'm after in the hope someone can assist me.

Point 1. I am trying to use an application.inputbox to ask the user to select a single range
Point 2. If the user presses 'Cancel' on the inputbox screen then a msgbox should display "Macro Cancelled" and the macro is ended.
Point 3. If the user presses 'Ok' when the inputbox is blank or the user selects a cell without a formula in then a msgbox should display "No formula in the selected cell. Please select a cell with a formula." and the macro is looped.

Note: If the user sees the message from Point 3 and loops back, pressing 'Cancel' now should still react like Point 2. (In my current code it just creates an endless loop - See below for more details)
Likewise if the user doesn't input anything into the inputbox and presses 'Ok' then the default excel message shouldn't pop up because I would like the msgbox from point 3 to show instead.

I have attached a sample workbook where range A1 contains a formula, Range A2 contains a value and all other cells are empty.
The code I currently have works if the user presses 'Cancel' before attempting to select a range, however if the user selects a range that doesn't contain a formula they get caught in an endless loop even if they press 'Cancel'. Also my code doesn't handle if a user leaves the inputbox blank and presses 'Ok'

This part of the code is part of a larger macro which actually asks a user for a 2nd input and then compares the run times of the two formulas. However I don't think that is needed so I have simplified the problem for now and hope to be able to scale it up.

Thank you in advance and I'll be around if you have any further questions or need any extra info.
EricDonk

Hi Eric,

Does this work for you:

Please Login or Register  to view this content.

Regards,

Robert

Hi Eric,

I don't use loops too often as I can't tell you the number of times I've had to force Excel to close because it gets stuck in a perpetual loop.

That said is this closer to the mark as it just leaves the input box open if ok is pressed but no cell has been selected:

Please Login or Register  to view this content.

Robert

Good luck with what you went back to try, hopefully you have more success than me because as you've probably realised your second attempt still does the infinity loop and doesn't use the custom error message for a blank entry into the inputbox.

Just updated the solution. Let me know how it goes.

leaving the inputbox blank and pressing 'Ok' because it just hides the error message rather than displaying a msgbox of my choosing.

I could be wrong but that's your only option as the code just won't get to the next command even with On Error Resume Next and Application.DisplayAlerts = False

An userform with a ref edit control may do the trick (refer attached).

I don't often use Labels, and GoTo Label, but it seems the best way here.

Please Login or Register  to view this content.

Hi Trevor,

Nifty code but the same issue arises like for my code if OK is pressed but the text box is empty even though you've used On Error Resume Next.

Robert

I know. Got rid of most of the issues though. No infinite loops. Just don't keep pressing Enter without selecting a range

Please Login or Register  to view this content.

I believe this amended code now solves the infinite loop.
After my comment that that the error flag didn't seem to be resetting, I decided to play around with your code TMS and had the idea to set the inputRng to Nothing at the start of every loop. This way if the user presses cancel then 'Nothing' stays as the inputRng and therefore allows the inputbox to be cancelled at any point.

Now I guess I need to play around with the Excel error when the inputbox is left blank, I'm hoping that if I can track down the err.number for it then I might be able to write something like if err.number = 277 Then msgbox "Input cannot be blank"

Finally I noticed TMS added some code about more than one cell being selected, this was a clever catch and not something I had thought about. Now I may switch my code to distinguish the difference between a blank cell vs a cell with a value just so the error message can be more specific.

We will get there, thanks for your messages and help so far TMS and Robert.

TMS thanks you for joining this thread, when I read your second message that you had beaten the infinite loop I was excited to try it, however unless I'm doing something wrong I still get it.
If the user selects a cell without a formula it produces the msgbox saying "No formula in the selected cell. Please select a cell with a formula." but then any further attempts to cancel the inputbox will now loop the user back to the same message rather than cancelling.

Hi Eric,

Try putting this line of code...

Please Login or Register  to view this content.

...immediately below this line:

Please Login or Register  to view this content.

Make sure to save the workbook before you try the revised code and have no other workbook open should you have to close Excel if it gets in an endless loop again.

Regards,

Robert

Originally Posted by Trebor76

Hi Eric,
Try putting this line of code...
...immediately below this line:
Make sure to save the workbook before you try the revised code and have no other workbook open should you have to close Excel if it gets in an endless loop again.
Regards, Robert

Thanks Robert that did the trick and we have finally beaten the endless loop or in this case endless label. Thank you so much
Honestly this forum is brilliant because of the awesome people, cheers to you and TMS for all the messages, code and help. I have now revised the macro to include your previous Application.DisplayAlerts = False and then True before and after the inputbox. I have also made a distinction between a blank cell and cell with a value in the msgbox that pops up:

Please Login or Register  to view this content.

I'm still looking into the possibility of getting the err.number of the error message that pops up when the inputbox is left blank and the user press 'Ok' (assuming alerts aren't switched to False) so that I can test if the line like the below might work?

Please Login or Register  to view this content.

EricDonk

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks Robert that did the trick and we have finally beaten the endless loop or in this case endless label. Thank you so much.

You're welcome and a big thank you to TMS 👍 Thank you also for the rep 😎

I'm still looking into the possibility of getting the err.number of the error message that pops up when the inputbox is left blank and the user press 'Ok' (assuming alerts aren't switched to False) so that I can test if the line like the below might work?

No - as I said earlier leaving the input open is your only option as far as I can tell unless you use the custom form option from my post# 9.

All the best,

Robert

Give this RefEdit Option a go...Covers all scenarios I think...
Custom design the form to your specs...

Please Login or Register  to view this content.

Where in the spreadsheet is the code mentioned in your post

Code is in the Userform...

Secondly some of the code is red

This is 64bit API declarations...still show as error in 64bit systems but does not matter...cannot be compiled...an does not cause an error...

And finally I'll change the code to not pop up the userform until the user clicks the button but likewise how do I stop it popping back up after a cell with a formula is selected?

Been resolved in new attached file...

And does it store the range so I can use it later in my code

If declared in same same procedure then yes...
Just Declare a Variable...

Please Login or Register  to view this content.

Not sure what you need to do after once you've captured the users selection...
Depending on this you can declare as Global, Private ,Public etc to be used with workbook...



THANKS.gif

I know Robert was hinting at this option as a way to go but thank you Sintek for doing it.

I also provided a workbook with an userform that had a RefEdit control.

Give this RefEdit Option a go...Covers all scenarios I think

Agreed the RefEdit is the best option (refer my reply #9). I really like the icon you've assigned the macro to 👍

Hi Eric,

Not sure if this is an option but can you create a named range for the cell(s) you want to check and loop through that range checking if any has a formula so the user won't have to select anything?

Robert

@ Robert...

I also provided a workbook with an userform that had a RefEdit control.

I only just realised after opening your file that all worked as expected...I assumed that as OP made no mention of this that it did not fulfill all requirements...Hence my updated version and remark...

Give this RefEdit Option a go

And yes...Great minds think alike...

Originally Posted by Trebor76

I also provided a workbook with an userform that had a RefEdit control.
Agreed the RefEdit is the best option (refer my reply #9). I really like the icon you've assigned the macro to 

Originally Posted by Sintek

@ Robert...
I only just realised after opening your file that all worked as expected...I assumed that as OP made no mention of this that it did not fulfill all requirements...Hence my updated version and remark...
And yes...Great minds think alike...

First of all I'd like to say sorry to Robert, I completely misunderstood your reply in post #9 and although I downloaded your sheet I thought it was an example scenario of how to use RefEdit rather than something you had made for me. I'm so silly because I read the code (in the module only - as I don't know how to read the code in the form) but never pressed 'play' as I didn't realise it was made for for exact scenario. I then closed it thinking that is something I'll look into if people can't help via the inputbox as I knew for now it was over my head. Apologies again, I feel so guilty and am sorry to Sintek for the extra work I created. RefEdit does the job perfectly and I have marked this as solved now thanks to the both of you.

Originally Posted by Trebor76

Hi Eric,
Not sure if this is an option but can you create a named range for the cell(s) you want to check and loop through that range checking if any has a formula so the user won't have to select anything? Robert

Thank you for the suggestion, but in this case that's how the code started, it checked every cell in every open workbook and tested any formulas for their efficiency. The data it created was cumbersome and lengthy so I set about to compare just 2 at a time because the likelihood is there will only ever be 2 or 3 ways a formula will be written and I want to evaluate the best one. The code I asked for help with is just a way to select cells, cancel if needs be and produce a custom error message if someone didn't select anything - All of which RefEdit solves. Thank you.

Originally Posted by Sintek

Code is in the Userform...
This is 64bit API declarations...still show as error in 64bit systems but does not matter...cannot be compiled...an does not cause an error...
Been resolved in new attached file...
If declared in same same procedure then yes...
Just Declare a Variable...
Not sure what you need to do after once you've captured the users selection...
Depending on this you can declare as Global, Private ,Public etc to be used with workbook...

Thank you for the reply Sintek, it was all very helpful. Cheers for explaining the 64bit API code and amending your file to not pop up the user form when opened and also end when a formula cell is selected, as Robert pointed out your button is very cool too.
In terms of what I'll be doing with the cell selected (and also another selected cell) is comparing the efficiency of the 2 formulas so I can best decided which is less intensive in a large scale spreadsheet, I have the code for that, so knowing I can set RefEdit1 as the range is helpful thank you.

To summarise, thank you to Robert and Sintek for resolving my original question by using RefEdit, I have marked this thread as solved. Also apologies to you both for the extra effort I caused by misunderstanding Roberts earlier post. I will have a google of how to edit the code with a user form and tinker with the code you both made because they produce the the same error regardless if a range of cells, a blank cell or a cell with a value is selected but I'll try and make a separate msgbox for each of those scenarios.

Cheers for all the help, it was really kind of you and I'm extremely grateful and once more, I am truly sorry for the misunderstanding.
EricDonk

as I don't know how to read the code in the form

Right click on the form and choose View Code.

I will have a google of how to edit the code with a user form and tinker with the code you both made because they produce the the same error regardless if a range of cells, a blank cell or a cell with a value is selected but I'll try and make a separate msgbox for each of those scenarios

Don't understand...Thought all was perfect...What are you referring to...

Post 24 has that option...
Just change the message...

Please Login or Register  to view this content.

It is all about the positioning of the check criteria...Edited Message to show selection options...

Please Login or Register  to view this content.

capture the full cell address (Workbook, sheet name and cell ref) of the user selected cell

Not sure what you wanna do with it but here you go...

Please Login or Register  to view this content.

Originally Posted by Sintek

Not sure what you wanna do with it but here you go...

Wow, I can only hope to be as good at VBA as you one day, that's fantastic, thank you so much! I spent about 3 hours trying to get it working and you came along and did it in minutes, very well done.

In answer to your question, I plan to turn the user form response into a full cell address that is pasted into my workbook. (In my case to Q4) That way I can re-run the body of my code without asking for the user to select the same cell again and again. The code you provided will be invaluable because to call the full address I'll use something like:

Please Login or Register  to view this content.

So thank you for you time and skills.

Glad to have contributed...Happy Coding....