• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Incremental stock number for vehicles

D

DPatrick

New Member
I have a drop down list of different car models and need to assign stock numbers to them. This has been working for years, but we've added a new model and none of them automatically assigns the next available stock number anymore. Any help is appreciated. Thanks

Here's the existing code for Module 1

Sub Increment_ModelNumber()
Dim ModelName As String
Dim ModelNum As String
Dim Prefix As String
Dim Suffix As String

ModelName = ActiveCell.Offset(0, 1).Value
ModelNum = Names(ModelName).Value

Prefix = Mid(ModelNum, 3, InStr(1, ModelNum, "-") - 2)
Suffix = Val(Mid(ModelNum, InStr(1, ModelNum, "-") + 1, 4)) + 1

Do Until Len(Suffix) = 4
Suffix = "0" & Suffix
Loop

ActiveCell.Value = Prefix & Suffix

Names(ModelName).Value = "=" & Chr(34) & Prefix & Suffix & Chr(34)
End Sub

Sheet 1 (code)

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Selection.Address, ":") > 0 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
Target.Offset(0, -1).Select
Increment_ModelNumber
Target.Offset(0, 1).Select
Application.EnableEvents = True
End Sub
 
Hi, DPatrick!
Consider uploading a sample file, it'd be much easier to test the provided code if related data is available.
Regards!
 
DPatrick said:
When I try to run the Macro I get
Run-time error '1004'
Application-defined or object-defined error
Click to expand...
Hi ,

There are a couple of problems in the system as designed.

1. The data validation list of car models has the model C-HR , but Excel will not accept a named range C-HR ; the named range which has been defined is CHR. You need to do something about this.

Either modify the DV list to use the model CHR , or within the code , strip the hyphen from C-HR , so that the named range CHR can be used.

2. I couldn't find the mode ScionXA in the list of defined named ranges.

When ever you introduce a new model , you need to do the following :

1. Include this model name in the DV list.

2. Create a named range using the name which has been used in the DV list , and assign a starting number to it. If the named range is left blank , then adding 1 to this , which the code is doing , will generate an error.

Narayan
 
I've corrected the models to match in the Name File and the Data Validation but still not getting a new stock number when I select a vehicle model from the drop down list. None of the models are automatically creating a stock number, even the ones that haven't been changed. I've uploaded the revised sheet if you're willing to take a look at it again.
thanks
 

Attachments

  • STOCK Updated (Autosaved).xlsm
    770.1 KB · Views: 2
Hi ,

I am not facing any problem.

I did the following :

1. Placed the cursor in B15251 , which was the first blank cell in column B after the last row of data in the worksheet , and selected Avalon ; immediately , the corresponding cell in column A , A15251 , was filled in with the number 17-1768.

2. In a similar manner , I continued to enter model numbers in B15252 , B15253 , B15254 ,... with other models and I did not face any problem.

3. I went to cell B15244 , and changed the model which was Avalon to Highlander ; immediately , the code changed the number in A15244 to the appropriate number for the Highlander model.

If nothing is happening at your end , it may be that macros are not enabled , so that the Worksheet_Change macro never gets executed.

Even if macros are enabled , it may be that at some point in time , the code statement :

Application.EnableEvents = False

has been executed , following which events will not get triggered , and consequently , the Worksheet_Change macro will never get executed. To revert to the normal condition , you need to execute the following statement :

Application.EnableEvents = True

Narayan
 
Macros are enabled under trust settings and it's not populating the stock number, so there must be an issue with the Application.EnableEvents = True settings. I see one True and one False, but not sure if both should be True.

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Selection.Address, ":") > 0 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
Target.Offset(0, -1).Select
Increment_ModelNumber
Target.Offset(0, 1).Select
Application.EnableEvents = True
End Sub
 
Hi ,

The code is perfectly OK , as it is.

The problem is when it generates an error , it has already turned off event detection , so before you do anything to the worksheet , after you have cancelled execution of the macro , you need to execute the statement :

Application.EnableEvents = True

in the Immediate window to restore the normal condition.

Narayan
 
I'm going to sound really dumb, but I had help creating this sheet 6 years ago and I'm a network guy trying to help the sales manager. If I open the sheet, before I pick any models, can I start the Application.EnableEvents = True function and save the sheet so that it will automatically run every time they open it?
 
Hi ,

When you run Excel initially , there will be no need to run this statement.

The problem crops up only if an error is generated and program execution is aborted when the statement :

Application.EnableEvents = False

has already been executed.

Only if the above has happened do you need to manually execute the statement :

Application.EnableEvents = True

Have you tried what I listed in my post ?

1. Run Excel.

2. Open the workbook.

3. Place the cursor in the first blank cell after the last row of data in column B.

4. Select any model using the drop down.

What happens if you follow this sequence of actions ?

Narayan
 
If I select a model from the drop down list in column B nothing happens, other than the car model in that box. No stock number appears for any model I choose.
 
I checked the Trust Center settings and Enable All Macros is checked. Trust access to VBA project is checked. Anywhere else it could be disabled?
thanks
 
Not sure how, but it's working now. I opened the original file that was saved last week and the new "autosaved" file started working.
thanks for the help
 
You must log in or register to reply here.
Back
Top