Create User-defined Template

[SeventhFloorProfessor]

I have a couple of general questions:

1) I am creating a macro that is attached to a Gradebook. The thing about
gradebooks is that every teacher has their preferred layout AND specific
gradebook programs that take .csv files require those .csv files to be in a
specific format. In order to let the user run my macro with any gradebook
layout, I'd like to be able to do something like this:

Have user select cell containing first assignment, then select second cell
containing the second assignment. Since gradebooks do follow a pattern (e.g.
Assignment Name #1 is in A1, Assignment Name #2 is in A3, Assignment Name #3
is in A5, etc) I would be able to let the macro run after it has this basic
information. The code for allowing the user to select the first two
Assignment Name cells is

Set assignmentNamerange1 = Application.InputBox(Prompt:="Please select the
cell in your GRADEBOOK with the FIRST Assignment Name.", Title:="Select
Assignment Name Cell", Type:=8)

Set assignmentNamerange2 = Application.InputBox(Prompt:="Please select the
cell in your GRADEBOOK with the SECOND Assignment Name.", Title:="Select
Assignment Name Cell", Type:=8)

What I would need is code that would allow the macro to compute the distance
between assignmentNamerange1 & assignmentNamerange2, and then store that as a
variable (nextCellassignmentname), and use that variable to move a specified
distance.

2) Let's say a teacher creates their gradebook using this macro. Is there
any VBA code that would allow the macro to recognize that it has already
created a template? I was thinking a userform that has some options on it
("Create New Template", "Create New Gradebook from Template", "Update
Gradebook from File", "Update Gradebook Manually").

3) And finally, is it possible to "protect" an excel file so that it can
only be used on a specific computer, or specific number of computers?

Thanks for your help! For some reason, I have trouble accessing this forum,
so if you ask a question and I don't reply for a couple hours, it's not for
lack of trying, but I will check back often!

 

[broro183]

hi

For number 1, this may help:


Code:
--------------------
Option Explicit
Sub Demo()
Dim assignmentNamerange1 As Range
Dim assignmentNamerange2 As Range
Dim nextCellassignmentname As Long
Set assignmentNamerange1 = Application.InputBox(Prompt:="Please select the cell in your GRADEBOOK with the FIRST Assignment Name.", Title:="Select Assignment Name Cell", Type:=8)
Set assignmentNamerange2 = Application.InputBox(Prompt:="Please select the cell in your GRADEBOOK with the SECOND Assignment Name.", Title:="Select Assignment Name Cell", Type:=8)
nextCellassignmentname = assignmentNamerange2.Row - assignmentNamerange1.Row
'example of moving a set destance...
msgbox assignmentNamerange1.Offset(nextCellassignmentname, 0).address
'your code...
Set assignmentNamerange1 = Nothing
Set assignmentNamerange2 = Nothing
End Sub
--------------------


For numbers 2 & 3, yes, this can be done (possibly using a "log file"
on a central drive) but depending on the inquisitiveness of users the
"protection" can be broken. If you upload a file with what you've got so
far, someone may be able to tidy it up for you.

hth
Rob