strange behaviour

[Risky Dave]

Hi,

I have a fairly complex workbook (that is being used as a database) whichis
exhibiting some strange behaviour.

The user enters a new record from a form that is called from a button click.
When the form is closed, the data is copied to pre-defined places on another
sheet. New lines are then created on several other sheets (all hidden) so
that various automated analyses can be carried out on the newly entered data.
The creation of these new lines includes adding some quite complex formulas
into some cells and adding data validation and conditional formatting to
others.

Each of these activities is done by a different sub called from the input
form.

When I run the various subs, they work perfectly - each sheet is set up
exactly as iI want it to be. However, when I try to run the whole lot
together most (but not all!) of the formatting in each of the subs fails. No
error messages are generated

The full code is large and complicated, so it wouldn't be appropriate to
post much of it here, but below is the bit that calls the subs from the form:
<some code here>
Set CurrentCell = CurrentCell.Offset(0, 4) ' response approach
CurrentCell.Value = ComboBox2.Value

New_Storage ' create storage area on history page

Date_New_Risk_Line ' date stamp the new line and add the unique
identifier

Format_New_Risk_Line ' format the new risk line

add_new_control_line ' update controls page with the new risk

add_new_mitigation_line ' update mitigations page

add_contingency ' update contingency page

add_new_assessment_line ' update the risk assessment page with the
new risk

<some more code here>

This is the first bit of code of one of the subs (they are all pretty
similar and all the variables are declared earlier) :

Sheets("Treatment - Controls").Unprotect
Set vNewRisk = Sheets("Treatment - Controls").Range("a8")
lLineCount = 8
Do Until vNewRisk.Value = "" ' look for first
blank cell
Set vNewRisk = vNewRisk.Offset(1, 0)
lLineCount = lLineCount + 1
Loop

sRiskNumber = Sheets("user data").Range("b7")
Set rLookUpRange = Sheets("identification").Range("a:d")
With vNewRisk ' put new risk
number into first blank line & format cell
.Value = sRiskNumber
.Interior.ColorIndex = 15
.Borders.LineStyle = xlContinuous
.Locked = True
End With
vTitle = Application.VLookup(sRiskNumber, rLookUpRange, 4)
Set vNewRisk = vNewRisk.Offset(0, 1)
With vNewRisk ' put new risk
title into first blank line & format cell
.Value = vTitle
.Interior.ColorIndex = 15
.Borders.LineStyle = xlContinuous
.WrapText = True
.Locked = True
End With

' put borders around cells
Sheets("Treatment - Controls").Range("a" & lLineCount & ":r" &
lLineCount).Select
With Selection
.Borders.LineStyle = xlContinuous
End With

The first couple of parts work ('Look for the first blank cell' and 'put new
risk number...'), but the vlookup does not work (I'd appreciate it if someone
could explain why not) and the formatting of the borders around a selection
of cells also does not work.

It's probably worth emphasising that this whole sub does work when run as a
standalone piece of code.

This is all in Office '07 under Vista, if that makes a difference.

My apologies for the length of this post and please let me know if I need to
provide more information.

TIA

Dave

 

[OssieMac]

Hi Dave,

This might not help at all but I have experienced strange behaviour with
assigning ranges to variables when trying to do it in one line like the
following.

Set rLookUpRange = Sheets("identification").Range("a:d")

Changing code to following then worked perfectly. I have no explanation for
it because I would have thought that both are the same thing but the
following worked as expected and the above method didn't.

With Sheets("identification")
Set rLookUpRange = .Range("a:d")
End With

Also with the following code and loop to find the first blank cell. Are
there any other cells with data below the first blank one?

Set vNewrisk = Sheets("Treatment - Controls").Range("a8")
lLineCount = 8

Do Until vNewrisk.Value = "" ' look for first blank cell
Set vNewrisk = vNewrisk.Offset(1, 0)
lLineCount = lLineCount + 1
Loop

If no other cells below with data then you could do it this way without the
loop.

With Sheets("Treatment - Controls")
Set vNewrisk = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
lLineCount = vNewrisk.Row