Data entry forms & a bit of VBA « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Data entry forms & a bit of VBA

(11 posts) (3 voices)

Started 1 year ago by AUTO
Latest reply from AUTO

Tags:

AUTO

Member
Joined: Nov '10
Posts: 34
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.
```
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
```
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.
Posted 1 year ago #
pob

Member
Joined: Oct '11
Posts: 5
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:
```
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):
```
Private Sub Worksheet_Activate()
    UserForm1.Show
End Sub
```
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
```
Posted 1 year ago #
AUTO

Member
Joined: Nov '10
Posts: 34

Thanks for the speedy reply pob.
I'll try it out and report back.
Although, probably not till tomorrow.
Shalom

Posted 1 year ago #
AUTO

Member
Joined: Nov '10
Posts: 34

Hi pob,
Unfortunately it doesn't work!
The code unprotects the "Monthly Sorted" sheet as before but not the "Data" sheet.
Any ideas?

Posted 1 year ago #
AUTO

Member
Joined: Nov '10
Posts: 34

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

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185
Hello Auto,
Perhaps you want:
```
me.showdataform
```
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
Posted 1 year ago #
AUTO

Member
Joined: Nov '10
Posts: 34

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?

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185
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:
```
Private Sub Worksheet_Activate()
    Me.ShowDataForm
End Sub
```
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
Posted 1 year ago #
AUTO

Member
Joined: Nov '10
Posts: 34

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

Posted 1 year ago #
asa

Member
Joined: Sep '11
Posts: 185

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

Posted 1 year ago #
AUTO

Member
Joined: Nov '10
Posts: 34

Thanks asa, that seems to have solved the problem.
I appreciate the time and effort you've expended on my behalf.
Shalom
Auto

Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics

» Excel for Beginners - Tutorials
» Excel Formula Examples
» Array Formulas
» Excel Charts
» Pivot Tables
» Project Management using Excel
» Excel Dashboards
» Excel VBA
» Advanced Excel

Our Products

Full Training Programs
» Excel School
» Excel Dashboard Classes
» Excel VBA Classes
» Excel Financial Modeling
» Excel for PMs
Crash Courses
» Excel Formula Course
» Excel Dashboards Course
Downloads
» Excel PM Templates
» Excel Formula E-book

About us

» About Chandoo.org
» Welcome
» Best of Chandoo.org
» FREE Excel Downloads
» Contact Details
» Excel Forums

Username	Password
Remember me