omagoodness
Board Regular
- Joined
- Apr 17, 2016
- Messages
- 59
Hello
I have a userform (frmMenItems) used for entering plate description data into a table. The form has an image Control (Image1) that the user can click and then select the appropriate picture from a folder on their computer. The path to the file is added to a textBox and then saved to the table data. When a record needs to be edited, the record is selected from a list box on the same userform. When the item is dblClicked, the table data is put in the form controls to allow for editing, including the image. So far, so good.
I have 3 further problems that I cannot get working. The table(tblMenus) has 46 columns (A:AV)
1) I cannot get the new image path to save when editing, and
2) After I enter a new record, the form clears and readies for the next new record, however the image control does not clear.
3) The image does nor unload when I clear the form.
All appropriate codes I am using are below: Thank you in advance.
The code I use to save new data and clear the form is:
This is the code to populate the form for editing from the list box
The code for the cmdEdit click to save changes:
Code to clear form
I have a userform (frmMenItems) used for entering plate description data into a table. The form has an image Control (Image1) that the user can click and then select the appropriate picture from a folder on their computer. The path to the file is added to a textBox and then saved to the table data. When a record needs to be edited, the record is selected from a list box on the same userform. When the item is dblClicked, the table data is put in the form controls to allow for editing, including the image. So far, so good.
I have 3 further problems that I cannot get working. The table(tblMenus) has 46 columns (A:AV)
1) I cannot get the new image path to save when editing, and
2) After I enter a new record, the form clears and readies for the next new record, however the image control does not clear.
3) The image does nor unload when I clear the form.
All appropriate codes I am using are below: Thank you in advance.
The code I use to save new data and clear the form is:
Code:
Private Sub cmdAdd_Click()
[COLOR=#008000]'set the variables[/COLOR]
Dim X As Integer
Dim nextrow As Range
On Error GoTo cmdAdd_Click_Error
If WorksheetFunction.CountIf(Sheet8.Range("C:C"), Me.menu1.Value) > 0 Then
MsgBox "This menu item already exists"
Exit Sub
End If
Set nextrow = Sheet8.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
[COLOR=#008000]'check that required fields are filled in[/COLOR]
Dim cCont As Control
For Each cCont In Me.Controls
If cCont.Tag = "Required" Then
If cCont = vbNullString Then
MsgBox "This field must be completed"
cCont.SetFocus
cCont.BackColor = vbYellow
Exit Sub
End If
End If
Next cCont
[COLOR=#008000] '*************************************************************************************[/COLOR]
[COLOR=#008000] 'Run the macro to calculate the total cost of the menu item and add the value to the form[/COLOR]
CalculateMenuCost
For X = 1 To 46
nextrow = Me.Controls("Menu" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
[COLOR=#008000] 'clear the controls[/COLOR]
For X = 1 To 38
Me.Controls("menu" & X).Value = ""
Next
[COLOR=#008000]'Run macro to display the saved message[/COLOR]
SaveMessage
[COLOR=#008000]'sort the Menu Cards[/COLOR]
SortMenuItems
[COLOR=#008000]'Assign the number for the next menu item[/COLOR]
'Dim MaxID As Integer
MaxID = Range("tblMenus[#Data]").Rows.Count
Me.menu2.Value = MaxID + 1
Me.menu3.Value = Date
[COLOR=#008000]'reinitialize the form for next record[/COLOR]
Me.lstMenu.RowSource = ""
Me.menu3.Value = Date
Set tbl = Sheets("Menu").ListObjects("tblMenus") 'Get the table
MaxID = Range("tblMenus[#Data]").Rows.Count
Me.menu2.Value = MaxID + 1
Dim Data, Dn As Long, Ac As Long, Ray
Data = Sheets("Menu").Range("C6", Sheets("Menu").Range("||AV" & Rows.Count).End(xlUp)).Value
ReDim Ray(1 To UBound(Data, 1), 1 To UBound(Data, 2))
With lstMenu 'Entries
.ColumnCount = 46
.ColumnWidths = "110,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"
End With
lstMenu.List = Data
SortMenuItems
Me.menu1.SetFocus
On Error GoTo 0
Exit Sub
cmdAdd_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAdd_Click of Form frmMenuItems"
End Sub
Code:
Private Sub lstMenu_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'dim the variables
Dim i As Integer
Dim strName As String
On Error Resume Next
'find the selected list item
i = Me.lstMenu.ListIndex
'add the values to the text boxes
Me.menu1.Value = Me.lstMenu.Column(0, i)
Me.menu2.Value = Me.lstMenu.Column(1, i)
Me.menu3.Value = Me.lstMenu.Column(2, i)
Me.menu4.Value = Me.lstMenu.Column(3, i)
Me.menu5.Value = Me.lstMenu.Column(4, i)
Me.menu6.Value = Me.lstMenu.Column(5, i)
Me.menu7.Value = Me.lstMenu.Column(6, i)
Me.menu8.Value = Me.lstMenu.Column(7, i)
Me.menu9.Value = Me.lstMenu.Column(8, i)
Me.menu10.Value = Me.lstMenu.Column(9, i)
Me.menu11.Value = Me.lstMenu.Column(10, i)
Me.menu12.Value = Me.lstMenu.Column(11, i)
Me.menu13.Value = Me.lstMenu.Column(12, i)
Me.menu14.Value = Me.lstMenu.Column(13, i)
Me.menu15.Value = Me.lstMenu.Column(14, i)
Me.menu16.Value = Me.lstMenu.Column(15, i)
Me.menu17.Value = Me.lstMenu.Column(16, i)
Me.menu18.Value = Me.lstMenu.Column(17, i)
Me.menu19.Value = Me.lstMenu.Column(18, i)
Me.menu20.Value = Me.lstMenu.Column(19, i)
Me.menu21.Value = Me.lstMenu.Column(20, i)
Me.menu22.Value = Me.lstMenu.Column(21, i)
Me.menu23.Value = Me.lstMenu.Column(22, i)
Me.menu24.Value = Me.lstMenu.Column(23, i)
Me.menu25.Value = Me.lstMenu.Column(24, i)
Me.menu26.Value = Me.lstMenu.Column(25, i)
Me.menu27.Value = Me.lstMenu.Column(26, i)
Me.menu28.Value = Me.lstMenu.Column(27, i)
Me.menu29.Value = Me.lstMenu.Column(28, i)
Me.menu30.Value = Me.lstMenu.Column(29, i)
Me.menu31.Value = Me.lstMenu.Column(30, i)
Me.menu32.Value = Me.lstMenu.Column(31, i)
Me.menu33.Value = Me.lstMenu.Column(32, i)
Me.menu34.Value = Me.lstMenu.Column(33, i)
Me.menu35.Value = Me.lstMenu.Column(34, i)
Me.menu36.Value = Me.lstMenu.Column(35, i)
Me.menu37.Value = Me.lstMenu.Column(36, i)
Me.menu38.Value = Me.lstMenu.Column(37, i)
Me.menu39.Value = Me.lstMenu.Column(38, i)
Me.menu40.Value = Me.lstMenu.Column(39, i)
Me.menu41.Value = Me.lstMenu.Column(40, i)
Me.menu42.Value = Me.lstMenu.Column(41, i)
Me.menu43.Value = Me.lstMenu.Column(42, i)
Me.menu44.Value = Me.lstMenu.Column(43, i)
Me.menu45.Value = Me.lstMenu.Column(44, i)
Me.menu46.Value = Me.lstMenu.Column(45, i)
strName = Me.lstMenu.Column(45, i)
Me.Image1.Picture = LoadPicture(strName)
Me.lblNew.Visible = False
Me.lblEdit.Visible = True
Me.cmdCancel.Visible = True
Me.lblPercent.Visible = True
Me.menu8.Visible = True
Me.cmdAdd.Visible = False
Me.cmdClear.Visible = False
Me.cmdEdit.Visible = True
Me.cmdDelete.Visible = True
On Error GoTo 0
End Sub
Code:
Private Sub cmdEdit_Click()
[COLOR=#008000]'declare the variables[/COLOR]
Dim findvalue As Range
error handling
On Error GoTo errHandler:
[COLOR=#008000] 'check for values[/COLOR]
CalculateMenuCost
If menu1.Value = "" Or menu2.Value = "" Then
MsgBox "Select a menu item from the list"
Exit Sub
End If
[COLOR=#008000] 'edit the row[/COLOR]
Set findvalue = Sheet8.Range("D:D").Find(What:=menu2, LookIn:=xlValues).Offset(0, -1)
For X = 1 To 46
findvalue = Me.Controls("menu" & X).Value
Set findvalue = findvalue.Offset(0, 1)
Next
[COLOR=#008000] 'run the macro to display the saved message[/COLOR]
SaveMessage
[COLOR=#008000] 'reinitialize the form[/COLOR]
Dim MaxID As Integer
Dim tbl As ListObject
Me.lstMenu.RowSource = ""
Set tbl = Sheets("Menu").ListObjects("tblMenus") 'Get the table
MaxID = WorksheetFunction.Max(Range("tblMenus[MenuID]"))
Me.menu3.Value = Date
Me.menu2.Value = MaxID + 1
Dim Data, Dn As Long, Ac As Long, Ray
Data = Sheets("Menu").Range("C6", Sheets("Menu").Range("AV" & Rows.Count).End(xlUp)).Value
ReDim Ray(1 To UBound(Data, 1), 1 To UBound(Data, 2))
With lstMenu 'Entries
.ColumnCount = 46
.ColumnWidths = "110,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"
End With
lstMenu.List = Data
Me.menu1.SetFocus
SortMenuItems
End Sub
Code to clear form
Code:
Private Sub cmdClear_Click()
Dim z As Control
Dim zz As Control
For Each z In frmMenuItems.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
For Each zz In frmMenuItems.Controls
If TypeName(zz) = "ComboBox" Then
zz.Value = ""
End If
Next zz
frmMenuItems.Image1.Picture = LoadPicture("")
Me.menu3.Value = Date
End Sub