Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 233
- Office Version
- 2016
- Platform
- Windows
Hello experts,
I have a simple form (pic att.) where it should show the picture of the person selected in the Drop box, any help is really appreciate it. I am also adding the code that I am using. The pics are saved in a file, but I am planning to save them later in SharePoint site.
VBA Code:
Option Explicit
Private Sub OpenForm()
UserForm1.Show
UserForm1.TextBox1.SetFocus
End Sub
Private Sub UserForm_Initialize()
Dim cMember As Range
Dim cScore As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupSheet")
'Getting the team members in the Dropbox
For Each cMember In ws.Range("MemberList")
With Me.cboMember
.AddItem cMember.Value
.List(.ListCount - 1, 1) = cMember.Offset(0, 1).Value
End With
Next cMember
'Getting the score numbers in the Dropbox
For Each cScore In ws.Range("ScoreList")
With Me.cboScore
.AddItem cScore.Value
End With
Next cScore
'Populating the date
Me.TxtDate.Value = Format(Date, "Medium Date")
'Me.txtComm.Value = ""
Me.cboMember.SetFocus
End Sub
Private Sub cboMember_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
On Error GoTo Errhand
If KeyCode = vbKeyReturn Then
Me.Image1.Picture = LoadPicture("C:\Users\gbarbosa\OneDrive - Lion Pty Ltd\Snagit" & cboMember.Value & ".JPG")
End If
Exit Sub
Errhand: MsgBox ("Picture not found, please check spelling or blah bla blah")
End Sub
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("DataSheet")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lPart = Me.cboMember.ListIndex
'check for a part number
If Trim(Me.cboMember.Value) = "" Then
Me.cboMember.SetFocus
MsgBox "Please select your name"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(lRow, 1).Value = Me.cboMember.Value
.Cells(lRow, 2).Value = Me.cboScore.Value
.Cells(lRow, 3).Value = Me.TxtDate.Value
.Cells(lRow, 4).Value = Me.TxtComm.Value
' .Protect Password:="password"
End With
'clear the data
Me.cboMember.Value = ""
Me.cboScore.Value = ""
Me.TxtDate.Value = Format(Date, "Medium Date")
Me.TxtComm.Value = ""
Me.cboMember.SetFocus
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Regards,
Gilly
I have a simple form (pic att.) where it should show the picture of the person selected in the Drop box, any help is really appreciate it. I am also adding the code that I am using. The pics are saved in a file, but I am planning to save them later in SharePoint site.
VBA Code:
Option Explicit
Private Sub OpenForm()
UserForm1.Show
UserForm1.TextBox1.SetFocus
End Sub
Private Sub UserForm_Initialize()
Dim cMember As Range
Dim cScore As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupSheet")
'Getting the team members in the Dropbox
For Each cMember In ws.Range("MemberList")
With Me.cboMember
.AddItem cMember.Value
.List(.ListCount - 1, 1) = cMember.Offset(0, 1).Value
End With
Next cMember
'Getting the score numbers in the Dropbox
For Each cScore In ws.Range("ScoreList")
With Me.cboScore
.AddItem cScore.Value
End With
Next cScore
'Populating the date
Me.TxtDate.Value = Format(Date, "Medium Date")
'Me.txtComm.Value = ""
Me.cboMember.SetFocus
End Sub
Private Sub cboMember_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
On Error GoTo Errhand
If KeyCode = vbKeyReturn Then
Me.Image1.Picture = LoadPicture("C:\Users\gbarbosa\OneDrive - Lion Pty Ltd\Snagit" & cboMember.Value & ".JPG")
End If
Exit Sub
Errhand: MsgBox ("Picture not found, please check spelling or blah bla blah")
End Sub
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("DataSheet")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lPart = Me.cboMember.ListIndex
'check for a part number
If Trim(Me.cboMember.Value) = "" Then
Me.cboMember.SetFocus
MsgBox "Please select your name"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(lRow, 1).Value = Me.cboMember.Value
.Cells(lRow, 2).Value = Me.cboScore.Value
.Cells(lRow, 3).Value = Me.TxtDate.Value
.Cells(lRow, 4).Value = Me.TxtComm.Value
' .Protect Password:="password"
End With
'clear the data
Me.cboMember.Value = ""
Me.cboScore.Value = ""
Me.TxtDate.Value = Format(Date, "Medium Date")
Me.TxtComm.Value = ""
Me.cboMember.SetFocus
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Regards,
Gilly