1.

Solve : Help debugging VBA for Excel 2000?

Answer»

Hey,

So I've been working for a bit on a little project in VBA for excel 2000. I believe there are easier ways to achieve the goal that is DESIRED, but I need to get more familiar with VB anyway, so bare with me.

Basically, this Macro (group really), adds a level of security to the worksheet. It starts by hiding Sheet1, then calling more stuff.

Code: [Select]Private Sub Workbook_Open()
Sheet1.Visible = xlSheetVeryHidden
Call Sheet1.Initialize_Authorization
End SubThis is in the workbook section.

There's a bit more than I want to type or paste here, so I'll add some links to my pastebin soon for reference, but the main problem that I'm running to now, is passing a value from a Sub in my UserForm to a Sub in the Sheet1 code...

UserForm2
Code: [Select]'Code 1
Public PermLevel As Integer

Public Sub OKBtn2_Click()
Select Case UserForm2.PasswordBox1
Case "12345"
PermLevel = 1
Call Perm_Processing
Case "67890"
PermLevel = 2
Call Perm_Processing
Case "kyle"
Perm Level = 3
Call Perm_Processing
Case Else
MsgBox "Error assigning permissions.", vbExclamation, "ERROR!"
End Select
End Sub

This all works fine for Perm_Processing, however, Sheet1 contains,

Code: [Select]'Code 2
'This is a test to debug further.

Public Sub Permission_Assignment
MsgBox "PermLevel is currently set to " & PermLevel, vbOKOnly, "Status"
PermiLevel = PermLevel
MsgBox "PermiLevel is currently set to " & PermiLevel,vbOKOnly, "Status"
End Sub
This returns PermLevel as being no value at all, not even 0.

Again, Code 1 is in UserForm2, and Code 2 is in Sheet 1.

Anyway, in the mean TIME, if anyone can tell me what I'm doing wrong, that'd be cool.

Otherwise, I'll put the whole pastebin together and probably have it linked in the next day or so.

Thanks!
-kyle_engineer
Permlevel is a public instance field of the UserForm. If you want a global variable, you need to declare it in a MODULE.

As a side note, if you put "Option Explicit" at the top of the Sheet's VBA Code it will flag Permlevel as not being declared. What it is doing is creating a new Variant variable when you use it. Ok. Cool. Thanks BC_Programmer.

I'll try all that, but it's unlikely that I won't be back with more questions. So, if I were to define each of the variables that I want to be globally shared, I could just define them in a module (under the Modules folder), then make the other sections of code Option Explicit?

Would this allow me to reference them whenever? Because I was reading something about variables being destroyed once their respective instance is stopped... Would this work, or am I thinking of this all wrong?

Most of my scripting/coding background is .bat, so even though VB is pretty old, I'm taking a big leap forward I think. And I've never been pro with .bat either. Lol! So sorry if I'm a little slow following.Quote from: kyle_engineer on December 05, 2012, 11:37:16 AM

So, if I were to define each of the variables that I want to be globally shared, I could just define them in a module (under the Modules folder), then make the other sections of code Option Explicit?
Yes and No. I'm basing this on Visual Basic 6, which is very similar to the VBA used in Word/Excel. My Experience with macros says it's exactly the same for this; and in Visual Basic 6, global variables had to be declared in a module, using either the "Public" or the "Global" keywords. (They ACTUALLY work the same).

Option Explicit is not required, however, it will flag when you use a variable you don't declare. In this case the use of the variable you thought would be publically accessible but wasn't would be flagged as undefined.



Quote
Would this allow me to reference them whenever? Because I was reading something about variables being destroyed once their respective instance is stopped... Would this work, or am I thinking of this all wrong?
Modules do not have "instances"; That is part of why they are required. With userForms, you can have two of the same UserForm present; if you declare a variable within that UserForm, those two instances are going to have distinct copies of that variable that are not the same. With modules, a publically accessible variable is accessible everywhere.

Ok. Cool.

So that worked for that part of the code. Now I have this properly passing from the userform to sheet1.

The next but is on this:

Code: [Select]'This ASSIGNS ranges to be used based on credentials.
Private Sub Range_Assignment()
Select Case PermLevel
Case "1"
Set ProtectedCells1 = Range("A:N")
Set ProtectedCells2 = Range("AT:AZ")
Case "2"
Set ProtectedCells1 = Range("K:M")
Set ProtectedCells2 = Range("AT:AZ")
Case "3"
AdminPerm = True
Case Else
MsgBox "There has been an error establishing your permissions. Please exit immediately.", vbExclamation, "WARNING!"
End Select
End Sub

'This block cells designated to be restricted based on user perm.
Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
If Not AdminPerm = True Then

'This is the beginning of the first limited range.
If Not Intersect (Target, Range(ProtectedCells1)) Is Nothing Then
Application.ScreenUpdating = False
Set ProtectedCell = Target
MsgBox "You do not have permission to access this cell.", vbExclamation, "Insufficient Permissions"
Application.ScreenUpdating = True
Else
Set OriginalCell = Target
End If

Basically, it checks each time a selection change is made, to see if the new cell is in a restricted range, and bounces back if it is.

However, the range assignment is the part I'm having trouble with. The columns that I need to block are infinite, in that there will always be additions to them. So how do I tell it to use a bottomless range? :s

I did find a little snippet that will return the last row number of a column/range, but it's just a number (like "14"). So it's not too helpful at the moment either...

I'll have to modify a copy of this project, but I think I can probably post it so that you can see a little more what it's all about... (If wanted.)Try using Sheet1.Range instead of just range and use the Union method.
Code: [Select] Set ProtectedCells = Union(Sheet1.Range("A:N"), Sheet1.Range("AT:AZ"))
for example

I was able to do this code:
Code: [Select]Option Explicit
Sub test()
Dim r As Range
Set r = Union(Sheet1.Range("A:C"), Sheet1.Range("E:G"))
r.Select
End Sub

And it selected A:C and E:G properly

(untested) Modification of your code you could try:

Code: [Select]'This assigns ranges to be used based on credentials.
Private Sub Range_Assignment()
Select Case PermLevel
Case "1"
Set ProtectedCells = Union(Sheet1.Range("A:N"), Sheet1.Range("AT:AZ"))
Case "2"
Set ProtectedCells = Union(Range("K:M"), Range("AT:AZ"))
Case "3"
AdminPerm = True
Case Else
MsgBox "There has been an error establishing your permissions. Please exit immediately.", vbExclamation, "WARNING!"
End Select
End Sub

'This block cells designated to be restricted based on user perm.
Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
If Not AdminPerm = True Then

'This is the beginning of the first limited range.
If Not Intersect (Target, ProtectedCells) Is Nothing Then
Application.ScreenUpdating = False
Set ProtectedCell = Target
MsgBox "You do not have permission to access this cell.", vbExclamation, "Insufficient Permissions"
Application.ScreenUpdating = True
Else
Set OriginalCell = Target
End If
Thanks Techno.

I'll slap this in my code and see how it runs.

If you feel like having another look at the script as a whole, I'll PM you the link to my pastebin.

THANKS!
kyle_engineer


Discussion

No Comment Found

Related InterviewSolutions