Vba Modification to Incorporate an Input Box

[The_Snook]

Morning all,

I use the following code to validate some dates in my data but I'm going to be handing the process over shortly so I'd like to modify the code to make it easier for my successor. The code applies a conditional format to the column called "Invoice Date" based on the month number I manually change each month (Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MONTH(" + InvDate + ")=6"). What I would prefer is an input box that only allows you to input a number between 1 and 12 and then incorporates that as part of the conditional format.

Please Login or Register  to view this content.

If you have any queries just give me a shout.

Thanks in advance,

Snook

[Olly]

A slight rewrite of your code, to avoid doing all that selecting:

Edit: I should have mentioned that the rewrite includes your requirement for an inputbox to choose a month number!!

Please Login or Register  to view this content.

[:) Sixthsense :)]

Where the inputbox value should be stored (In which variable) ?

[Richard Buttrey]

Hi,

You'll need to Dim an additional month number variable.

Also note that rarely is is necessary to use .Select. This adds a time overhead since VBA needs to first jump back to Excel and then jump back to VBA which with some code, e.g. loops can seriously slow things down. So avoid .Select and the similar .Activate wherever possible. Just work directly with the object.

Also note it's good programming practice to have some system of identifying what the variables are. For instance I generally preface String variable names with "st", Long variable names with "l", Boolean variable names with "b", ...etc. with relatively short code this may not seem a big deal but with longer code and more variables it helps when debugging code and examining the value of variables. I've not modified your variables but the Month Numebr variable for the Input box is a Long variable hence lMonthNo. Note there's a school of thought that using the Integer variable is not needed since VBA automatically converts Integers to Long 'behind the scenes', and hence you can avoid that step by always using Long variables rather than Integer.

So try

Please Login or Register  to view this content.

[The_Snook]

Thanks all. Is it possible to build in an error message if the value input isn't valid?

Snook

[Olly]

Thanks all. Is it possible to build in an error message if the value input isn't valid?

Sure - see the additional rows highlighted in red:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

One way, immediately after the End With instruction add the following two lines

Please Login or Register  to view this content.

[The_Snook]

Absolutely poifect!

Cheers Olly and Richard, much appreciated.