Hi all,
ok i will make this as easy as possible, i need to convert all of my userform VBA back to work in a worksheet, elliminate the userform totally.
following is all of my userform code. if anyone can please help me to do this i would greatly appreciate any help.
My Mum wants to use my workbook but only uses a third party program so if i convert back to just a worksheet and save as an xls or xlsx file it should work.
Thankyou again in advance to any and all help.
ok i will make this as easy as possible, i need to convert all of my userform VBA back to work in a worksheet, elliminate the userform totally.
following is all of my userform code. if anyone can please help me to do this i would greatly appreciate any help.
Code:
Private Sub CommandButton5_Click()
Worksheets("Shopping List").Range("B3").Value = Me.rcpIngredients2.Value
Sheets("Shopping List").Visible = True
Sheets("Shopping List").Select
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Sheets("Shopping List").Range("B2:B50").Select
Selection.PrintOut
Sheets("Shopping List").Visible = True
End Sub
Private Sub CommandButton6_Click()
Worksheets("Cooking Card").Range("F2").Value = Me.TextBox3.Value
Worksheets("Cooking Card").Range("B3").Value = Me.rcpMethod2.Value
Sheets("Cooking Card").Visible = True
Sheets("Cooking Card").Select
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Sheets("Cooking Card").Range("B2:B36").Select
Selection.PrintOut
Sheets("Cooking Card").Visible = True
End Sub
Private Sub btnStart_Click()
Dim TimeOut As Single
TimeOut = Timer + (TimeValue(tBox1) * 86400)
While Timer <= TimeOut
DoEvents
tBox1.Value = Format((TimeOut - Timer) / 86400, "h:mm:ss")
Wend
tBox1.Value = "0:00:00"
Application.Speech.Speak "Your Cooking time has finished"
MsgBox "Times Up"
End Sub
Private Sub CommandButton1_Click()
Application.Visible = True
Me.Hide
End Sub
Private Sub ComboBox4_Change()
'Declare Variables
Dim RecipeIDRow As Range
With Me
If .ComboBox4.ListIndex = -1 Then Exit Sub 'If Blank Value then....Exit Sub
'Find Recipe Number (ID) In Column 2 of Recipes Worksheet
Set RecipeIDRow = Sheets("Recipes").Columns(2).Find(Me.ComboBox4.List(Me.ComboBox4.ListIndex, 1))
'Set the textbox values
.rcpNumber1.Value = RecipeIDRow.Value
.TextBox3 = RecipeIDRow.Offset(, 1) 'Offset 1 Column Over i.e. Recipe Name...
.rcpIngredients2.Value = RecipeIDRow.Offset(, 2) 'Offset 2 Column Over i.e. Recipe Ingredients...
.rcpMethod2.Value = RecipeIDRow.Offset(, 3) 'Offset 3 Column Over i.e. Recipe Method...
.Notes2.Value = RecipeIDRow.Offset(, 4) 'etc...
.nutInfo4.Value = RecipeIDRow.Offset(, 5)
.nutInfo5.Value = RecipeIDRow.Offset(, 6)
.rcpDate.Value = RecipeIDRow.Offset(, 7)
'dim the variables
Dim i As Integer
Dim fPath As String
Dim Pic2 As String
'set the file path
fPath = ThisWorkbook.Path & "\" & "Images"
i = Me.ComboBox4.ListIndex
On Error Resume Next
'display the picture
Me.Pic2.Picture = LoadPicture(fPath & "\" & Me.ComboBox4.Column(0, i) & ".jpg")
'If recipe picture is not available
If Err = 53 Then
Me.Pic2.Picture = LoadPicture(fPath & "\" & "NoPicture.jpg")
End If
'reset error handler
On Error GoTo 0
End With
End Sub
Private Sub ComboBox5_Change()
'Declare Variables
Dim LRow As Long
'Define Last Row
LRow = Sheets("Recipes").Cells(Sheets("MyLists").Rows.Count, "A").End(xlUp).Row
With Me
'Clear Search ComboBox Values
.ComboBox4.Clear
'Reset TextBox Values
.rcpNumber1.Value = vbNullString
.rcpIngredients2.Value = vbNullString
.rcpMethod2.Value = vbNullString
.Notes2.Value = vbNullString
.TextBox3 = vbNullString
.TextBox5 = ComboBox5
.nutInfo4.Value = vbNullString
.nutInfo5.Value = vbNullString
For LngLp = 2 To LRow
'If the Category matches our combobox then add to the list....
If Sheets("Recipes").Range("A" & LngLp).Value = .ComboBox5.Value Then
With .ComboBox4
.AddItem Sheets("Recipes").Range("C" & LngLp).Value 'Add Recipe Name (Column 1)
'Note there are two columns in this combobox
'The columns start at 0 and go up from there....
'I am loading the unique ID into Column 1 (which is really the second column)
'If you look at properties for combobox 4 you will see that I have
'Set Columns to 2 as well as set the widths of the columns to 150 pt;0 pt
'The 0 pt.... essentially hides the ID
'We need the ID in order to identify the row we need to look up in
'The ComboBox4_Change Event.....
.List(.ListCount - 1, 1) = Sheets("Recipes").Range("B" & LngLp).Value 'Add Unique Identifier (Column 2)
End With
End If
Next LngLp
End With
End Sub
Private Sub CommandButton3_Click()
'Save New Recipe to Workbook Recipes Sheet
Dim irow As Long
Dim ws As Worksheets
'find first row in database
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
Cells(erow, 1) = TextBox5.Text
Cells(erow, 3) = TextBox3.Text
Cells(erow, 4) = rcpIngredients2.Text
Cells(erow, 5) = rcpMethod2.Text
Cells(erow, 6) = Notes2.Text
Cells(erow, 7) = nutInfo4.Text
Cells(erow, 8) = nutInfo5.Text
End With
TextBox5.Value = ""
TextBox3.Value = ""
rcpIngredients2.Value = ""
rcpMethod2.Value = ""
Notes2.Value = ""
nutInfo4 = ""
nutInfo5 = ""
End Sub
Private Sub UserForm_Initialize()
'Declare Variables
Dim LRow As Long
'Define Last Row
LRow = Sheets("MyLists").Cells(Sheets("MyLists").Rows.Count, "A").End(xlUp).Row
'Load Category Values from MyLists
With Me
.ComboBox5.RowSource = "MyLists!" & Sheets("MyLists").Range("A2:A" & LRow).Address
.ComboBox5.ListIndex = 0 'Set ComboBox5 Value to first in list
lblTimer = "00:00:00"
End With
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
End SubMy Mum wants to use my workbook but only uses a third party program so if i convert back to just a worksheet and save as an xls or xlsx file it should work.
Thankyou again in advance to any and all help.
