Help with Commands and Syntax please.
I have done some programming, but never any VBA in excel. I am needing
some help writing 2 ON EVENTS and 1 Function listed below. The logic
works, but the commands and Syntax are what I need help with.
ANY help would be greatly appreciated :-)
_______________On OPEN EVENT_____________________
This will tell me how many lines of data are on the sheet
when it is opend. (The + 5 is to allow for my headers)
Calls to the Function below.
ON OPEN EVENT()
Set DATALINES = 0
STart Loop
Datalines = Datalines + 1
IF CHKROW(DATALINES+5) = BLANK Then END
ELSE LOOP
_______________FUNCTION__________________
This Function returns the Status of the Row sent to it
and Returns Either FULL BLANK OR OUT
FUNCTION CHKROW(RTBC (ROW TO BE CHECKED)
IF ((RTBC < DATALINES) or (RTBC DATALINES)) THEN RETURN OUT
IF COUNTBLANK(RTBC 2:RTBK 6) = 4 RETURN BLANK
Else Return FULL
END FUNCTION
_______________ON CHANGE EVENT___________________
This Will check if the ROW Data was just changed is in the Data
field Range and adds or removes rows.(Calls on Function CHKROW Above)
ON EVENT CHANGE(
SET CROW = Row of the cell data changed in
SET CCOL = Collumn of the cell data changed in
IF (CCOL < 2) or CCOL 6 ) Then END
CR = CHKROW(CROW)
NR = CHKROW(CROW +1)
IF (CR=OUT or (CR=BLANK AND NR=OUT)) THEN END
IF (CR=FuLL AND NR=OUT) THEN
Copy ROW CR on Sheet1
Insert it at ROW CR+1 on Sheet1
Copy ROW CR on Sheet2
Insert it at ROW CR+1 on Sheet2
(Only copy Formating and Formulas not Values)
DATALINES=DATALINES + 1
END
If (CR=BLANK AND NR=FULL) THEN
DELETE ROW CR on SHeet1
DELETE ROW CR on SHeet2
DATALINES=DATALINES - 1
END
END EVENT
|