Input Box Help Needed

[BallGazer]

Hi Guys

I hope someone can help me. I have an input Box that ultimately creates a new line of data on a Sheet. This works fine. However every time this form is activated I need to display the "next available" index number in the first box on the form. The index numbers are listed on a sheet called "DataSource" at Range("L2:L500"). I guess that once a number is used I would have to set some kind of flag to indicate it can't be used again. If I knew the code I could place this in the code for the "Save & Exit" Button. Any help with suitable code for the allocation of (and cancellation of) the next number and would be appreciated by this relatively Amateur VBA user.

Regards

BallGazer

 

[BrianB]

Can't you just look up the last number added to the sheet and add 1 ?

 

[BallGazer]

Thanks for your prompt reply. Unfortunately life is not that easy. The main problem I have is the code neceeasy to fire off the input form scan the list for the "next" number (which is in fact Alpha Numeric). and slot it into the Input Box field.

Regards

BG

 

[MickG]

Try this out for a trial.
Enter some Numbers in column "A" run the code .
The code "InputBox" will dispay the first number in Column "A" , if you Click "OK" the number will be selected and the Cell will be coloured "Yellow". The next time you run the code the Next (Not coloured Yellow value) will show in the "InputBox".

[COLOR=navy]Sub[/COLOR] MG11Dec19
[COLOR=navy]Dim[/COLOR] Message, Title, Default, MyValue, temp [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dn.Interior.ColorIndex = 6 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]Set[/COLOR] temp = Dn
        [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
Message = "The next Available number is :- " & "" & temp & ""   
    Title = "Get Index Number"
        MyValue = InputBox(Message, Title, temp)
            [COLOR=navy]If[/COLOR] MyValue = "" [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
            [COLOR=navy]Else[/COLOR]
                temp.Interior.ColorIndex = 6
            [COLOR=navy]End[/COLOR] If
MsgBox MyValue
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[BallGazer]

Thanks Mick

I'll give this a try later. I'll let you know how I get on.

Regards

BG