Input Box Help Needed

BallGazer

Board Regular
Joined
Jul 16, 2008
Messages
110
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

:confused:
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Can't you just look up the last number added to the sheet and add 1 ?
 

BallGazer

Board Regular
Joined
Jul 16, 2008
Messages
110
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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".
Code:
[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
 
Last edited:

BallGazer

Board Regular
Joined
Jul 16, 2008
Messages
110
Thanks Mick

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

Regards

BG

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,123
Messages
5,599,834
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top