User Form for adding and viewing Pictures

[LarryLaser]

Hey All,
I'm working on a project for my wife, she is a teacher, and not very computer Literate. So I need this Grade Book to do everything she needs with the least amount of Tech-kNowledge needed to perform.

Attached is a copy of the WorkBook and the folder for Pictures (some misc pics from her camPhone)

here is what is next:

User Form for adding and viewing Pictures of Students
Details:
Form to be shown when cell in (Range.Name("Pic")) is selected or active.
Show UserForm1 (Macro ShowPicForm) fires.
If cell has data, Show "UserForm1 (MultiPage2)", shows pic of student linked to cell (row).

"CommandButton3" ("Close") "OnClick", Form Unloads and Function (Sub NextRow) is fired, "NextRow" selects 1 row down, column "C" is selected.

If cell is empty, Show "UserForm1 (MultiPage1)", gives option (CommandButton1) to link cell to picture in "Pics" folder. (Pics folder is included in same folder as Grade Book).


Form Functions and Objects:
"TextBox", needs to Get Name of student from same row {active cell} (=CONCATENATE('Student Info'!D8," ",'Student Info'!C8)) (*NOTE* D8-D43 = Range.Name("Student_First_Name"), C8:C43 = Range.Name("Student_Last_Name")), then,

"CommandButton1" ("Pick Photo") "onClick" will open "Insert Pic" dialog box in "Pics" (dir same folder as Grade Book) folder to select picture (*.jpg Image), If Pic is selected, pic is renamed to match "TextBox"(*.jpg).

"CommandButton2" ("Go Back to Grade Book") when pic is selected, "OnClick", Form Unloads and Function (Sub NextRow) is fired, "NextRow" selects 1 row down, column "C" is selected.

If pic is not selected "TextBox" and Active cell is cleared.

"CommandButton4" label reads ("Cancel")."OnClick", Form Unloads and Function (Sub NextRow) is fired, "NextRow" selects 1 row down, column "C" is selected.

*Special Notes* (Togle Button upper left corner)
Range Of "Student Info" is Visable, but Range of "Private Info" is not visable or selectable unless Unlocked with password. When ToggleButton is depressed I want a Message box with a textbox requiring a password to unhide the Private info section. How do add this functionality?

[lucas]

Hello Larry,
I doubt if anyone will take on this large of a project all at once. Now that you've outlined it you should ask specific questions, one step at a time.

For instance it now apprears that your next step is to figure out how to import or link your pictures along with any steps you have taken to accomplish that task.....

[LarryLaser]

Hello Steve
You are correct, I am not looking for a complete solution all at once. I just figured it would be easier to list the details in the first post so you XLMasters have an understanding of the project and post pointers on where to find information on code snipets to accomplish pieces of the project.
Of all the XL related sites I am registered on , THIS ONE IS THE TOP.

At the moment I am working on the Protected Section.

*Special Notes* (Togle Button upper left corner)
Range Of "Student Info" is Visable, but Range of "Private Info" is not visable or selectable unless Unlocked with password. When ToggleButton is depressed I want a Message box with a textbox requiring a password to unhide the Private info section. How do add this functionality?

I have changed the ToggleButton to a CommandButton, which fires a userform2 with textbox and commandbutton,
[vba]Option Explicit
Private Sub CommandButton1_Click()
If TextBox1 = "password" Then
ActiveSheet.Unprotect
Range("PrivateInfo").EntireColumn.Hidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
Else
MsgBox "Private Information remains protected"
Unload Me
End If
Unload Me
End Sub
[/vba]
On the Protected Section I added another CommandButton2 that fires,
[vba]Private Sub CommandButton2_Click()

ActiveSheet.Unprotect
Range("PrivateInfo").EntireColumn.Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select

End Sub
[/vba]
OnClick the "Private Info" section hides but the CommandButton2 Does not, and "CommandButton2.Hidden = True" is not supported.
any Ideas on how to hide the button when Private Info section is hidden?

[lucas]

Not sure how to hide it but you can disable it I think:
[VBA]CommandButton2.Enabled = False[/VBA]
It will be greyed out....

[LarryLaser]

Hey Steve
I got it

In Design Mode go to Object Properties (rightClick menu) and change the "Placement" Property from '2' to '1' and the Command Button maintains Its position on the Sheet.

[mdmackillop]

As a start
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'by malik641 @vbxpress
If Target.Column = 7 Then ViewPicture Target[/VBA]
and
[VBA]
Sub ViewPicture(Target As Range)
UserForm1.Show
If Target.Row > 7 And Target <> "" Then
UserForm1.Image2.Picture = LoadPicture(Target.Text)
End If
End Sub

[/VBA]

[LarryLaser]

Hey MD
I have tried to work out the code to function piece by piece, and tried to change it to work for named ranges and keep getting errors or no function.

I am trying to gain a greater understanding of the proper syntax for vba code, and living with migraines. My level of frustration peeks around 2am every day.

Details:
Form to be shown when cell in (Range.Name("Pic")) is selected or active.
Show UserForm1 (Macro ShowPicForm) fires.
If cell has data, Show "UserForm1 (MultiPage2)", shows pic of student linked to cell (row).

If cell is empty, Show "UserForm1 (MultiPage1)", gives option (CommandButton1) to link cell to picture in "Pics" folder. (Pics folder is included in same folder as Grade Book).

"TextBox", needs to Get Name of student from same row {active cell} (=CONCATENATE('Student Info'!D8," ",'Student Info'!C8)) (*NOTE* D8-D43 = Range.Name("Student_First_Name"), C8:C43 = Range.Name("Student_Last_Name"))

If cell has data, Show "UserForm1 (MultiPage2)", shows pic of student linked to cell (row).

I appreciate all the help I get from this site and Greatly Appreciate any Study material that will help me to understand VBA/Procedures.

Thanks Again MD

Larry

[LarryLaser]

How do I integrate this code properly so it does not damage the existing operation?

As a start
[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'by malik641 @vbxpress
If Target.Column = 7 Then ViewPicture Target

[/vba]
and
[vba]

Sub ViewPicture(Target As Range)
UserForm1.Show
If Target.Row > 7 And Target <> "" Then
UserForm1.Image2.Picture = LoadPicture(Target.Text)
End If
End Sub

[/vba]

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'by malik641 @vbxpress
If Selection(1, 1).Row < 8 Or Selection.Row > 43 Then Exit Sub

ActiveSheet.Unprotect

'This next piece makes sure that enough info is entered to actually "Autonumber" the row
Dim rng As Range
Set rng = Range("C" & Selection.Row, "L" & Selection.Row)

'Makes sure that "C#" through "L#" (-"G") have info in it before continuing
Select Case True
Case rng(1, 1) = "": GoTo ExitHere
Case rng(1, 2) = "": GoTo ExitHere
Case rng(1, 3) = "": GoTo ExitHere
Case rng(1, 4) = "": GoTo ExitHere
Case rng(1, 6) = "": GoTo ExitHere
Case rng(1, 7) = "": GoTo ExitHere
Case rng(1, 8) = "": GoTo ExitHere
Case rng(1, 9) = "": GoTo ExitHere
Case rng(1, 10) = "": GoTo ExitHere
End Select

Dim Acronym As String

' by lucas @vbxpress
Dim RowOffset As Long
Dim IndexCol As String
'Set values
RowOffset = -7

Acronym = Left(Range("District"), 1) + _
Left(Range("School"), 1) + _
Left(Range("TeacherLast"), 1) + _
Left(Range("TeacherFirst"), 1) + ("-") + _
Left(Range("ClassRoom"), 1) + ("-") + _
Left(Range("Grade"), 1) + _
"-"

'Change the B to the column where you want the numbers to show
IndexCol = "B"

Intersect(ActiveCell.EntireRow, Columns(IndexCol)).Value = ActiveCell.Row + _
RowOffset

With Intersect(ActiveCell.EntireRow, Columns(IndexCol))
.Value = ActiveCell.Row + RowOffset
.NumberFormat = """" & Acronym & """00"
End With

ExitHere:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub


[/vba]

[mdmackillop]

Add the new line line in the first section immediately after the comment as shown. Paste the new sub routine after your End Sub statement.
I kept it separate to allow further development without messing up your own code.

[LarryLaser]

My Bad,
It wasn't your code that caused the error, It was because of this
"Case rng(1, 5) = "": Goto ExitHere "
I removed this and was testing after I posted.

[LarryLaser]

I am working with named ranges, not real knowledgable yet .

I have a "UserForm1" ("UF1") that initalizes when a cell is selected (Range.Name("Pic"), The "UF1" is MultiPage (2), page1 has 1 "TextBox1" and 3 CommandButtons.
Here is my Question.
When the "UF1" Initalizes, I need the "TextBox1" to be populated from 2 cells in the same row as the selected cell but 2 different named ranges. The formula would be like this "=CONCATENATE('Student Info'!D10," ",'Student Info'!C10)", Range C8:C43 = "StudentLast" and D8;d43 = StudentFirst.
Then I want the "TextBox1" to populate the selected Cell after clicking on CommandButton1. This I can handle

If you would Like I can attach a copy of the porject.
Any help with my project would be appreciated.

[LarryLaser]

Figured it out
in the UF1 Module I added;
[VBA]Private Sub UserForm_Initialize()
TextBox1.Text = ActiveCell.Offset(0, -3) & " " & ActiveCell.Offset(0, -4)
End Sub[/VBA]

[LarryLaser]

solved part

Form to be shown when cell in (Range.Name("Pic")) is selected or active.
Show UserForm1 (Macro ShowPicForm) fires.
If cell has data, Show "UserForm1 (MultiPage2)", shows pic of student linked to cell (row).

"CommandButton3" ("Close") "OnClick", Form Unloads and Function (Sub NextRow) is fired, "NextRow" selects 1 row down, column "C" is selected.

and,

If cell is empty, Show "UserForm1 (MultiPage1)", gives option (CommandButton1) to link cell to picture in "Pics" folder. (Pics folder is included in same folder as Grade Book).

Form Functions and Objects:
"TextBox", needs to Get Name of student from same row {active cell} (=CONCATENATE('Student Info'!D8," ",'Student Info'!C8)) (*NOTE* D8-D43 = Range.Name("Student_First_Name"), C8:C43 = Range.Name("Student_Last_Name")), then,

Here's the code that works so far.
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Column = 7 And Target.Row > 7 Then ViewPicture Target

'UF1
Private Sub UserForm_Initialize()
TextBox1.Text = ActiveCell.Offset(0, -3) & " " _
& ActiveCell.Offset(0, -4)
End Sub

'Mod1
Sub ViewPicture(Target As Range)
Dim fPath As String
fPath = ThisWorkbook.Path & "\Pics\"
If Target.Text <> "" Then
UserForm1.Image2.Picture = LoadPicture(fPath & Target.Text & ".jpg")
UserForm1.Show
Else
If Not Target.Text = 0 Then
UserForm1.MultiPage1.Value = 0
UserForm1.Show
End If
End If
End Sub
[/vba]
Now trying to get the Insert Picture Dialog to function correctly where the user can select the picture and LoadPicture in Image1.
Any Ideas??

[LarryLaser]

next part Solved

"CommandButton1" ("Pick Photo") "onClick" will open "Insert Pic" dialog box in "Pics" (dir same folder as Grade Book) folder to select picture (*.jpg Image)

input from Andy Pope

Private Sub CommandButton1_Click()
    Dim fPath  As String
    Dim fdgPicker As FileDialog
    fPath = ThisWorkbook.Path & "\Pics"
    ChDrive fPath
    ChDir fPath
    'Create a FileDialog object as a File Picker dialog.
    Set fdgPicker = Application.FileDialog(msoFileDialogFilePicker)
    With fdgPicker
        .InitialView = msoFileDialogViewThumbnail
        .Filters.Add "Graphics Files (*.bmp; *.gif; *.jpg; *.jpeg)", "*.bmp;*.gif;*.jpg;*.jpeg"
        .FilterIndex = 1
        If .Show = -1 Then
            Image1.Picture = LoadPicture(.SelectedItems(1))
        Else
            MsgBox "You have not selected a picture"
        End If
    End With
End Sub