Quality check: when inputing excel data.

[ravikr2678]

Hello all,
I need some help in doing a quality check while entering data in to excel file..
I have an excel file which has three sheets,
I need to implement quality check on sheet2 data.
'i.e' For Eg: I have a range of data in the sheet2 which is updated every week, say (A1:F6), There needs to be Msg box poped up to the end user if he tries to enter data any where other than A1:F6, saying that "Invalid Input"

Can this be possible..?

I'm new to VBA, So any help regarding this is greatly appreciated.
Thanks in advance

[anandbohra]

Try this
this has to be paste in required sheet
in your case sheet2
right click on it & click view code

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:F6" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
MsgBox "Invalid Input"'Change as per your requirement & also include your name in title
With Target
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub[/vba]

[ravikr2678]

Many Many thanks..
This works !!!

Appreciate your effort, Again..Thx u !!

[ravikr2678]

Hi,

I need to present an excel sheet (which has few macros ) to the clint,
But i want to make sure that the code in macros is locked so as to protect it from being modified accidentally even again...after submitting.

Can some one let me know how to go about this..

Appreciate your help.

Thank you.

[anandbohra]

To protect the MACRO coding follow these steps.
1. Open the excel file containing macros
2. Click Alt + F11 to go to visual basis editor
3. Go to Tools - VBAProject Properties....
4. Under protection tab u can set password & lock project from viewing.

Things to be kept in mind.

Project password is generally not recoverable. only if u have good password cracker software.
Demo software will not broke the password if u set it too hinh & alphanumeric
so when u protect the same always keep it atleast 8-10 character long & make it alphanumeric.

[Bob Phillips]

On the first point, why don't you just unlock the valid cells then protect the worksheet?

[ravikr2678]

Thank you so much for your ontime support.
It works!!

Have a good one.

[anandbohra]

On the first point, why don't you just unlock the valid cells then protect the worksheet?

Hi XLD this might be because when I started learning VBA I was more interested to protect & do small things through VBA rather than Excel inbuild features.

otherwise u r right the same can be done in Excel sheet only without VBA its just to protect the sheet with leaving specified cells unlocked.

This is my personal experience might not fit in above case.

[gwkenny]

Just a general note.

NOTHING ON THIS PLATFORM IS REALLY SECURE

lol

If someone wanted to go through the password on your workbook, whether it be the File Open password, workbook password, sheet password, or VB Project Password, it can be done.

The best way to truly protect your password is to put your code into a DLL.

Having said that, 99% of the people out there wouldn't know how to circumvent passwords. It's just that dang 1% you have to worry about!



g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[anandbohra]

Just a general note.

NOTHING ON THIS PLATFORM IS REALLY SECURE

lol

If someone wanted to go through the password on your workbook, whether it be the File Open password, workbook password, sheet password, or VB Project Password, it can be done.

The best way to truly protect your password is to put your code into a DLL.

Having said that, 99% of the people out there wouldn't know how to circumvent passwords. It's just that dang 1% you have to worry about!



g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

Will be glad if u throw more light on how to put code in DLL or create XLL as it is faster than VBA code.

pl share as much information as u have coz u r right as one can broke passwords.

surprisingly Analyzer XL / Trader XL the Technical analysis package for excel which costs somewhere around $250 ony of my friend has broken it thorugh VBA Password software.

[Bob Phillips]

It is not a trivial exercise to create DLLs and especially XLLs. They require another language tool, such as C++, VB, C#, etc., and requires learning how to use automation to access the Excel object model.

As for AnalyzerXL, TraderXL, they might just be standard VBA addins.

[anandbohra]

Hi XLD

Which one will be best for Excel automation as u mentioned
C++, VB, C# ???

Means I had some sort of VB Knowledge.

So creating XLL will be possible in VB (Visual Basic) ???

pl help.

& also suggest if u have some reference material available or post somewhere them. References like ebooks for the same.
The above one is just to enhance knowledge.
if u thinks that this work is not so easy then I will join Programming course for the same. (pl guide whether to join programming course or reading ebooks with example will be enough)

[Bob Phillips]

As with everything else mate, it depends.

If you want to go the dotNet route, you should go VB.Net or C# (Personally, I would go C#).

If you want to tread a familiar path, then VB is the way to go, as it is very similar to VBA, but understanding that MS no longer support VB. Doesn't mean it doesn't work, just that it is out of support and is going nowhere (a bit like VBA).

If you know C or C++, then use that, it will be the quickest, and allow you to do things that are a tad harder in VB, probably impossible in C#.

But no, you cannot do XLLs in VB or C#, that has to be C or C++.

As for reference material, a good starting point on automation, including XLLs, is the Professional Excel Development by Stephen Bullen, Rob Bovey, and John Green.

Is it easy? No. Can you do it wiothout a course? That would depend upon you, your aptitude, your knowledge and skills, and so on.

[anandbohra]

Thanks XLD
for the information provided.

hope some day some other site/ forum we meet discussing C language queries
I hope u will be available there for helping me & other same way u performed in this site.