Data entry forms a bit of VBA

[AUTO]

Hi Folks,

A few days ago Luke was kind enough to help me with a bit of code which worked a treat.

It was to except a sheet from being protected. I need to add another sheet, called "Data" to the exception rule.

I Tried to adapt the code Luke sent me but failed miserably (

Here is the code for which I need the further exemption.

[pre]

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Monthly Sorted" Then
With ws
.Protect Password:="les", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End If
Next ws
End Sub

[/pre]
Also, I wonder if its possible to have a data entry form "pop-up" when a sheet is selected (more VBA I suspect).

Hope this is making sense.

 

[pob]

You have a couple of options really, you can use an If..ElseIf..End If set-up or a Select Case set-up, which may be easier for what you want, as there are oddities involved in the first choice if due care isn't taken; for the Select Case just change the code as below:

[pre]

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
Select Case .Name
Case "Monthly Sorted", "Data"
Case Else
.Protect Password:="les", UserInterfaceOnly:=True
.EnableOutlining = True
End Select
End With
Next ws
End Sub

For the other bit, yes, with VBA, as you say. Just add this to the relevant worksheet code (and change it to match your userform name):

[pre][code]Private Sub Worksheet_Activate()
UserForm1.Show
End Sub

[/pre]

If you want the user to be able to use the workbook at the same time, or select another sheet and have the form autohide then use these instead:

Private Sub Worksheet_Activate()
UserForm1.Show vbModeless
End Sub

Private Sub Worksheet_deactivate()
UserForm1.Hide
End Sub[/code][/pre]

 

[AUTO]

Thanks for the speedy reply pob.

I'll try it out and report back.

Although, probably not till tomorrow.

Shalom

 

[AUTO]

Hi pob,

Unfortunately it doesn't work!

The code unprotects the "Monthly Sorted" sheet as before but not the "Data" sheet.

Any ideas?

 

[AUTO]

Belay that command Mr. pob.

My mistake, it works just fine. (forgot to unprotect on first use)

The second part of my original post was concerning the basic Data entry form not the "user form".

Hope this is still making sense and thanks again for your help.

Shalom

 

[asa]

Hello Auto,

Perhaps you want:

[pre]

me.showdataform

[/pre]
However, your macro will be paused until the user closes the data entry form. The data entry form is application modal - the user won't be able to click on another sheet, and your macro will pause execution until they close the form themselves.


You could put the above code line in the Worksheet_Activate event. Since the user has to close the form themselves, no code is needed in Worksheet_Deactivate.


Asa

 

[AUTO]

Hi asa,

Thanks for your reply.

I'm pretty sure that this is what I'm after. but, how to implement it!

I'm a real VBA novice, Do I just right click the sheet in question and click "View Code" then paste it in?

 

[asa]

Hi!

Yes, you can use View Code just as you describe, or you can:


Hit the "Visual Basic" button on the left side of the Developer ribbon, or press ALT-F11 which will bring up the Visual Basic Editor.


Then in the Project pane, find the name of the worksheet you want this to happen with, and double-click it.


Click into the code pane (main VBA editing window), and paste:

[pre]

Private Sub Worksheet_Activate()
Me.ShowDataForm
End Sub

[/pre]
You can close the Visual Basic Editor when you've done that if you wish. When you save the workbook, make sure to save it as a macro0enabled workbook if using Excel 2007 or 2010.


Asa

 

[AUTO]

Hi!

Did as instructed and get error as follows:-

Runtime error '1004'

Method 'showdataform' of object '_worksheet'


Hope this means something to you, as it means nothing to me )

Auto

 

[asa]

I think it means Excel didn't find a table of data to do entry in.


For no errors or popup messages, try this:

* Make sure the first row of the worksheet has column headings,

* Add at least one row of data

* Convert the range to a Table if you are using Excel 2010 (don't think this is supported in earlier versions) or highlight the first row and format it differently, i.e. bold, so Excel will automatically guess they are column headings.


You can get past the runtime error by just doing step 1 above, but without the other steps you will get a message requiring the user to confirm that the first row contains column headings.


Asa

 

[AUTO]

Thanks asa, that seems to have solved the problem.

I appreciate the time and effort you've expended on my behalf.

Shalom

Auto