Posts by JimmyB

Hey Carim, Thanks for your help. I will be assessing all of my macros to determine; (Multiple cell ranges as Selection) and (Single cell ranges as ActiveCell.) . Going forward I'll be using the proper designations. The program works well, so the fix will be prioritized as last though LOL.
Do you agree with the method of using userinterface code in conjunction with the 3 step protect method where applicable?
I value your opinion.Thanks Jimmy

Hi Roy. Thanks for your help but this method locks everything up (near as I can figure) weather the cells are locked or unlocked. I need the versatility, to have appropriate individual cells on various sheets unlocked, as the patient will be inputting data in some of them. So I appreciate your help, but will use the Unlock; Input; Lock method that Carim, has offered. Perhaps to reduce the likelihood of the problem, that you had previously mentioned regarding the error occurrence, I could use this on individual sheets that are not altered...such as the charts. If it wouldn't be too much trouble could you change the code to signify current worksheet only? Very Much appreciated...Jimmy

Well Carim I'm pretty good at Excel, but am new to VBA. I've had some training in Basic (many years back), which I must admit, helps me today as I do understand the concepts and lingo of programming. Recently I've been learning VBA through the painful process of hunting down examples from the net and applying them to build my macros. In the last 3 or 4 months I've struggled through, and successfully created a program that links about 25 sheets including 14 charts, which all finally work great. Now of late, I'm targeting the syntax that I've not been able to figure out on my own; so I thought that this forum would be the way to go...I was right. Soooo...as you've advised, I'm including a copy of one of my simpler macros (kind of long & structured like an amateur (me)). You've shown me the difference between a selection and active cell but I haven't yet cleaned that part up. I do include REM statements throughout my macros.
Problem:
I'm drawing data from my Home Sheet and static (mins & maxs) from my Default Sheet. I'm inputting them into a table in Weight Data Sheet, which in turn populates a chart I want to write to my table in Weight Data Sheet and copy data from my Default Sheet...both protected.
Please excuse the syntax faux pas :thanx: (Pointers are welcome)
Thanks...Jimmy

Sub PopulateWeight()


Dim Count
Dim Row
Dim LastCell
Dim FirstCell
Dim Last As Long


Sheets("Home Sheet").Select
Range("F3").Select
ActiveCell.FormulaR1C1 = "=Today()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Converts date formula to date value on Home Sheet


'*****Weight*****
Sheets("Home Sheet").Select
Range("F3").Select
Selection.Copy
Sheets("Weight Data").Select
Call Col001_FirstBlankCell.Column1_FirstBlank
ActiveSheet.Paste
Selection.Borders.LineStyle = xlNone
'Copies date to Weight Data


Sheets("Home Sheet").Select
Range("C3").Select
Selection.Copy
Sheets("Weight Data").Select
Range("A1").Select
ActiveSheet.Paste
Selection.Font.Size = 16
ActiveCell.Interior.Color = vbYellow
Selection.HorizontalAlignment = xlCenter
Selection.WrapText = True
Selection.Font.Bold = True
Sheets("Home Sheet").Select
Range("C3").Select
Selection.Copy
Sheets("Weight Chart").Select
Range("A1").Select
ActiveSheet.Paste
Selection.Font.Size = 20
ActiveCell.Interior.Color = vbYellow
Selection.HorizontalAlignment = xlCenter
Selection.WrapText = True
Selection.Font.Bold = True
'Copies patients' name from Home Sheet to Weight Data & Weight Chart


Sheets("Home Sheet").Select
Range("B23").Select
Selection.Copy
Sheets("Weight Data").Select
Call Col002_FirstBlankCell.Column2_FirstBlank
ActiveSheet.Paste
Selection.Interior.ColorIndex = 0
Selection.Borders.LineStyle = 0
Sheets("Default Sheet").Select
Range("C20").Select
Selection.Copy
Sheets("Weight Data").Select
Call Col003_FirstBlankCell.Column3_FirstBlank
ActiveSheet.Paste
Selection.Font.Size = 16
'Copies weight target to Weight Data


With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
End With


Sheets("Home Sheet").Select
'Goes to Home Sheet for next command


Range("F22").Select
Range("F22:H22").Merge
ActiveCell.Font.Size = 20
ActiveCell.Interior.Color = vbYellow
ActiveCell.Interior.Color = vbBlue
ActiveCell.Interior.Color = vbRed
ActiveCell.Font.Color = Black
ActiveCell.Interior.Color = vbYellow
ActiveCell.FormulaR1C1 = "ENTERED"
ActiveCell.Font.Size = 24
ActiveCell.Font.Size = 20
ActiveCell.Font.Color = vbRed
ActiveCell.Font.Bold = True
ActiveCell.HorizontalAlignment = xlCenter
'This Flashes to Show the patient that the data has been entered in Weight Data sheet


Sheets("Weight Data").Select
Last = Sheets("Weight Data").Cells(Rows.Count, "A").End(xlUp).Row
If Last > 115 Then
Call RngDelWT
End If
'This decides if there are 115 rows of data & what to do
Sheets("Home Sheet").Select
End Sub

Hi Carim. That sounds easy. I can live with that.
Someone else responded with the code:
ActiveSheet.Protect "", userinterfaceonly:=True [B1] = [A1] I tried this but couldn't get it to work. I tried putting it in my macro and putting it on the sheet...to no avail. (obviously I did put my password in the quotations). Leaving for the morning and will respond this afternoon when I get back...Thanks...Jimmy

I assume that my password goes in the quotations

Thanks a lot Kenneth. I appreciate it. I'll try it out tomorrow.
Jimmy

Looking for code that I can add to my worksheet that:
When a person inputs data in one unprotected cell, my macro is able to make the necessary calculations and write in the password protected cell. Now It does not write in it, as it is protected. Help will be greatly appreciated. Thanks JImmy

Thanks for your help Carim...You've saved me...have great day
Jimmy

Yeah I've got that. Thanks. I've used Selection & ActiveCel quite often and will revert to ActiveCell in future if that's preferred

Hey Carim...You're awesome! That got me into the sub just fine...Thanks so much
Well I chose 'Selection' to identify the last item that I selected when I chose the last entry in column 'A" ... 'Selection'
It's worked in the past on certain statements...Bad programming?...still got a lot to learn

Hey Carim...You're awesome! That got me into the sub just fine...Thanks so much

I need to set a limited amount of rows of data. Struggling with the syntax as it doesn't recognize the first line in my IF statement I'm kind of new to VBA so be gentle.LOL. Here is the code that I've created In Excel 2007

Sheets("Glucose Data").Select
Range("A65536").End(xlUp).Select

'Selects the last cell with data in column A

' It jumps over the if/then statement below and goes right to End Sub
' Something wrong with my IF statement and can't figure out the syntax
' I've tried assigning a variable to the selection & using that in my block to no avail. Spent hours with different variations
HELP!

If Selection > Cells(166, 1) Then

' At row 166 I want to call the subroutine

Call RngDel
Sheets("Home Sheet").Select
End If
End Sub