asked on

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"
ActiveWorkbook.Save

If ChngRow.Characters.Change Then

fnGetMaterial

Else
End If
Else

End If

Open in new window

Any input is greatly appreciated - I just need to get used to the VBA in the Excel environment

Norie

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?

dawber39

ASKER

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

Norie

How do you validate the number?

Do you look it up somewhere?

Check it against an exising list?

dawber39

ASKER

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
Sample.xlsm

dawber39

ASKER

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

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dawber39

ASKER

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

dawber39

ASKER

Can always count on an answer - or a push in the right direction - you folks are great