Learning vba

One of the easiest ways to learn Excel macros is to record them then edit the code to see what you've recorded.

This does not however work as well with Access macros – creating macros doesn't let you into the code.

Improved chicken wrap

This is a weekly prep kind of idea:
2 shredded chicken breasts
2 thick cut slices ham diced
Toss in 1-2 cups bbq sauce
Shake and store

Spoon 3 tbsp onto tortilla with
Splash of ranch
Sprinkle shredded cheese
Heaping tbsp diced onion and mixed pepper
2 strips cooked bacon
1 handful spinach.

Even better grilled.

A few formulas

LabelName: format([Table].[Column],"#.##,0")
This will format the data in the [Column] of [Table] (in Access) to 0.00 with leading zeros.

NoComma: Replace([Table].[Column],",","")
This will replace any , with nothing when displaying the data from [Table] in [Column]

Dynamic Drop downs

So you want a color selection based on a product listing…

Private Sub Worksheet_Change(ByVal Target As Range)

This sheet has the product selection in column C (3)

'If Target.Address = "$B$2" Then
If Target.Column = 3 Then

Dim sProduct As String, sFilter As String
sProduct = Target.Value

We’re going to check the Color sheet for the product code – there’s not very many rows of products in this example – only rows 2 to 5 hold the product types.

Product Name look up reference column


For iRow = 2 To 6
If Sheets("Color").Range("U" & CStr(iRow)).Value = sProduct Then
sFilter = Sheets("Color").Range("V" & CStr(iRow)).Value
sFilter = "=OFFSET(Color!$" & sFilter & "$2,0,0,COUNTA(Color!$" & sFilter & ":$" & sFilter & ")-1,1)"
End If
Next iRow

Next we delete and recreate the data validation lists based on the filter we just created looking up the products.

Range("F" & Target.Row).Validation.Delete
Range("G" & Target.Row).Validation.Delete
If Len(sFilter) > 0 Then
If sProduct = "Add-On" Then
Range("F" & Target.Row).Validation.Add xlValidateList, , , sFilter
Else
Range("F" & Target.Row).Validation.Add xlValidateList, , , "=Color!$D$2:$D$3"
Range("G" & Target.Row).Validation.Add xlValidateList, , , sFilter
End If
End If

End If

End Sub

Chicken wraps

Instapot 2 chicken breasts, plain. 

Shred into a plastic container and toss with about 2 tbsp BBQ sauce ( we like kraft garlic and more sauce isn’t a bad thing) 

Once cooled place in the centre of your favorite wrap with shredded mozzarella, a squirt of bacon ranch dressing and a handful of spinach. 

Panini press or grill until cheese is melted. 

Cut into pin wheels.