Excel Coding Part II

As some of you already know, especially FarWest - I am working on an Excel Workbook that is used to take orders. I am not used to coding in Excel- as much as I am in Access - however, instead of trying to fix everything that is wrong with this WB - I instead have chosen to write a function that would take care of the error at hand. Here's what I am doing- After the Retrieve Materials button is pushed - a message box will pop stating "Task Completed" Staring with hitting Okay on the msgBox:

Wait 2 seconds (for materials list to populate)
Save workbook
User enters or changes a number in C17 to C500 - then a qty and Unit of Measure - store info etc..and hits Validate and Save
Check the range for the cell that changed - if there is no change - its finished
If Changed - select cell that changed
Run a function that validates the material number
Validate the changed cell - if valid, the user validates and saves again -and finished
If invalid - msgBox tells them so - and cell gets cleared and info is reentered

This is where I am so far with it - I am getting some object and variable errors. I put a message box in there just to test break points. It was working until I added more. If someone can point me in the right direction that would be great, and there will probably be more questions with regard to this particular build

Private Sub Validation()
Dim MatLst As Range
Dim MatNum As Long
Dim ChngRow As Variant
Dim ChngCell As DataObject

Set MatLst = Sheets("Template").Range("C17:C500")
Set ChngRow = (MatLst + ChngCell)

If Len(MatLst.Address) > 0 Then

MsgBox "Range has Material Numbers Listed"

If ChngRow.Characters.Change Then


End If

End If

Open in new window


Any input is greatly appreciated - I just need to get used to the VBA in the Excel environment
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
What are you trying to do here?
Set ChngRow = (MatLst + ChngCell)

Open in new window

Firstly you can't set a range like that and secondly MatLst hasn't actually been set or given a value.

This expression Len(MatLst.Address) will always be >0 so the code in the If statement will always be executed.

Here I'm not sure what ChngRow is actually meant to be as you've declared it as Variant.

If it's a reference to a single cell then it will have a Characters property but there is no Change method/property associated with that.

Do you have a workbook in progress? If so can you attach it?

Also, what error is it you are trying to deal with?
dawber39Database Analyst / Application DeveloperAuthor Commented:
I will post the error - I ran into - and I will post a sample WB when I log on to my Work Computer. This way you can see the range I am dealing with. There is much code behind it - and I am told - I am not to re-write it - just fix the problem. - which is: If the Material list is populated, and someone changes a number - that number has to be validated. So I thought it would be simpler to just create a function that does that - and call to it during the Validate_And_Save function. There is code that should handle that - but it obviously doesn't work. I will populate the material list for you - you will not be able to call the web-services without the Network Credentials. Will post shortly
NorieAnalyst Assistant Commented:
How do you validate the number?

Do you look it up somewhere?

Check it against an exising list?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

dawber39Database Analyst / Application DeveloperAuthor Commented:
Yes- it looks it up through a web service pulling Teradata. I was getting Mismatch errors - but in in its current state, it will bring up the message box I have in my code, even with the range empty (Not needed - just there to see where I get to in break mode). What I am trying to do - is check for a change within the range - focus on that change - call the web service against it - to validate - and then message appropriately the code I started is in module 3 - and I will probably windup placing it in the WorkSheet_Change -or Validate_And_Save Functions. I appreciate your help with correction of how to declare the range and what not - I will attempt to write the code - but I might need guidance along the way
dawber39Database Analyst / Application DeveloperAuthor Commented:
There are validation lists in hidden sheets - but they do not contain the material list. If you could just tell me the best way to declare the range, a changed cell, and to point to the changed cell after a validation runs - I can move further from there - and then if more questions arise - I'll be back
NorieAnalyst Assistant Commented:
If you want to refer to a changed cell you should be looking at using a worksheet Change event.

That will be triggered whenever a cell is changed on a worksheet and it passed a range argument that refers to the cell/range that has been changed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawber39Database Analyst / Application DeveloperAuthor Commented:
I found the problem within that huge abomination of code. The code actually checks each iteration of the material number as it progresses, and I thought it was just stopping t the first one. When each number is checked, it returns with either the number and appropriate info for that number, and if it is valid it returns a [ ] symbol. This is the key - because there is an if statement that says if it is returned like that, then the cell = " " then gives the testing function a PASSED - which in turn directs the code progression to skip over the MsgBox  "9988898897 is an invalid Material number". So - I removed the line giving it a pass - created another variable with an if statement and placed it into the existing if statement with the MsgBox, and added and end function thereafter. - Works perfectly - thank you for all your help. I will have to post this comment in several other places. Thanks again guys - you got me looking in the right places
dawber39Database Analyst / Application DeveloperAuthor Commented:
Can always count on an answer - or a push in the right direction - you folks are great
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.