Replies

That last line is, unfortunately, not the case. Both parts of the And are evaluated regardless.

Sorry about that, I ran a test to confirm and you’re correct, I must have been thinking in another
language because I know I’ve programmed in languages that shortcut an AND if the left side is
false…more efficient.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

One thing you can do is set it to open in the last database used as follows:

Click the Office Button
Select the Advanced Options button at bottom of window
Click on Advanced in the Left pane
Check the “Open las used database when Access starts” checkbox
OK

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

I think it would depend on your situation, but you could also try filtering the data, or loading it into an array to read, before checking the values and colouring if appropriate.
There is also no point to the Is Nothing test in this line:

Code:

Loop While Not rngChroma Is Nothing And rngChroma.Address  firstAddress

since you won’t be in the loop if the range is nothing, and the .address check would error if it were Nothing.

Am I missing something?

If you look at it like this: Loop While (Not (rngChroma Is Nothing)) and (rngChroma.Address firstAddress)

The left side of the AND is TRUE only when rngChroma is not empty
and FALSE when rngChroma is empty … remember the Not reverses things.

Thus, the second half of the test is only evaluated if rngChroma has data which is what is the desired state.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

WsRyan,

In the Property Sheet for the ComboBox control select the Event Tab.
In the On Change event click in the box to the right then click on the button
with the three periods and select Event Procedure.

Code:

Private Sub Combo0_Change()

  MsgBox "The ComboBox Value has Changed", vbOKOnly, "Combo Box Status:"
  '***Replace with our code to update date***

End Sub

I hope this points you in the right direction.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

I ran a test and the function will work with one minor change: Define ZX as Object vs Collection.

Code:

Option Explicit
'*** Place this code in a Module --- not the form ***
Public Function TestAreAnyTrue(ZX As Object) As Boolean
               Dim ctl As Control
               TestAreAnyTrue = False
               For Each ctl In ZX
                  If ctl.Value = True Then
                    TestAreAnyTrue = True
                  End If
               Next ctl
End Function

Calling code in form process/OK button:

Code:

Private Sub cbProcess_Click()

   Debug.Print TestAreAnyTrue(UserForm1.frOne.Controls)
   Unload Me

End Sub

Results of two runs…1. All false 2. One true

Code:

False
True

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

It seems that you could save a lot of time by using what you have and creating a couple of lookup tables in a linked sheet/workbook.
Then replace the formulas in the contract with vlookup’s or hlookup’s depending on the table setup. Of course you would have to designate cells in the contract worksheet to identify things like county, tax status, etc. and use these references in the lookup formulas.
Also investigate the use of the indirect() function to grab the values in these designated cells and plant them in the lookup formulas.
I don’t have enough information from your example to know if the pricing is fixed or if you need a table for this based on the size of the property but this is also doable in the same fashion.

I’ve included a file I used to teach a course. Check out the Data tab and the formula for Salary. Hopefully this will better explain what I tried to say above.

Good luck.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

mbarron,

I couldn’t get your code to run as posted.
I made the following changes to get it to run.

Code:

Option Explicit
Dim dClick

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("e8")) Is Nothing Then
Exit Sub
Else
    Range("e7").Value = "EN " & Format(Val(Right(Range("e7").Text, 3) + 1), "000")
    dClick = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("e8")) Is Nothing Then
    Exit Sub
Else
If dClick = True Then
    dClick = False
    Exit Sub
Else
    Range("e7").Value = "EN " & Format(Val(Right(Range("e7").Text, 3) + 1), "000")
End If
End If
End Sub

EDIT: Actually I went back and checked and the .Text isn’t need, but doesn’t hurt, but the .Value is.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

If you’re using Firefox try the FireFTP addin.
You can do it all in your browser.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Lastyear contains a RELATIVE reference and hence issuing Goto will have different results if your starting cell is different,

Duh! Sorry I missed this.

I’ve been playing with the file back and forth with Win XP-Excel 2003 (totally stable) and
Win 7-Excel 2007 (totally flaky). It’s got me totally stumped.

Have you tried recreating the file from scratch and doing an initial save as .xls?
The file may have been corrupted when you did the conversion to .xls from .xlsx.
However, it is really strange that this only affects the way it works in 2007 and not 2003.
Go figure.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Darsha,

It appears that the rng.rows.count will only parse the first range in a non-contiguous range set.
See the test code below:

Code:

Sub Test()

Dim n   As Long
Dim Rng As Range
Dim Cnt As Long

Cnt = 0
Set Rng = Selection
  Debug.Print "Range: " & Rng.Address & " Rows: " & Rng.Rows.Count; ""
For n = 1 To Rng.Rows.Count
 Cnt = Cnt + 1
'Debug.Print Rng.Cells(n, 1)
Next n
  Debug.Print "n= : " & Cnt
  Debug.Print "Range: " & Rng.Address & " Rows: " & Rng.Rows.Count; ""

End Sub

------------- OUTPUT -----------

Range: $A$1:$J$13,$M$22:$S$28 Rows: 13
n= : 13
Range: $A$1:$J$13,$M$22:$S$28 Rows: 13

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Boobounder,

As a first shot I tried changing B5 to the following:

=CONCATENATE(“Inflation is “,TEXT(inflationRate,”##”),”%”)

No GO!

Next I listed all your names see below:

a_Hundred =100
Athens =Sheet1!$B$16:$J$16
Carthage =Sheet1!$B$15:$J$15
chosenCity =Sheet1!$B$4
ChosenVolume =Sheet1!$B$20:$J$20
Cities =Sheet1!$A$15:$A$19
cityCaption =Sheet1!$D$4
CityVolumes =Sheet1!A$15:A$19
inflationCaption =Sheet1!$D$5
inflationRate =Sheet1!$B$5
LastYear =Sheet1!IV:IV —- There is nothing in this column
priceCaption =Sheet1!$D$3
PriceScenario =Sheet1!$B$23:$J$23
PriceTable =Sheet1!$A$8:$B$10
Revenues =Sheet1!$B$25:$J$25
Rome =Sheet1!$B$19:$J$19
scenario =Sheet1!$B$3
Scenarios =Sheet1!$A$8:$A$10
Sparta =Sheet1!$B$17:$J$17
startYear =Sheet1!$B$12
Troy =Sheet1!$B$18:$J$18
Year =Sheet1!$B$12:$J$12

LastYear is used in this formula:

B23:J23 =IF(Year=startYear,INDEX(PriceTable,scenario,2),LastYear*(1+inflationRate/a_Hundred))

If I use the F5 (GoTo) key to find: LastYear it highlights a different Column (very strange) — Still no data!
Note: Re doing the Paste List of names still shows LastYear as above?

Seems like something has become seriously corrupted in this file.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

T.

Just a thought, but did you do a straight paste or a paste values.
You may have copied a reference to the original file if you did a straight paste.

R.G.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Gary,

Thanks, another trick for my bag!

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

This seems like such a handy thing that I tried it in Word 2007 and I cannot get it to work. I changed names slightly, checked program Help which suggested including the full path to the macro, used the When and Name labels, did not include the lblMessage.Caption to make it simpler. No error message. The form displays and never closes.

Code:

Sub TestTimedMessage()
  Application.OnTime When:=Now + TimeValue("00:00:05"), Name:="Normal.TestMacros.UnloadTimedDialog"
  frmTimedDialog.Show
End Sub

Sub UnloadTimedDialog()
  frmTimedDialog.Hide
End Sub

What am I missing? Security is wide open.
Thanks, Kim

I tried it in Word and also ran into problems.
1. Make sure it’s in a module not ThisDocument
2. Change the form’s behavior to ShowModal = False (this makes it work!)

I looked for the ShowModal property in the Object browser but could not find it so
you’ll have to set it when you design the form vs at runtime.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

I had a little trouble getting this to work…of course it was because I put the message display BEFORE the timer…duh!

Here’s my example code:

Code:

Sub Testit()

  ufMessageBoard.lblMessage.Caption = "Testing timed message"
  
  'Application.OnTime Now + TimeValue("00:00:03"), "UnloadMessageBoard"  'This works also
  Application.OnTime DateAdd("s", 3, Now), "UnloadMessageBoard"
  
  ufMessageBoard.Show
  
End Sub

Sub UnloadMessageBoard()

   ufMessageBoard.Hide
   
End Sub

ufMessageBoard is just an Excel form with a single label field.
I used a label field because it won’t appear like a text field
which may be inviting the user to enter something.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs