Replies

Hi Wassim,

As you suggested, we found the eml file in the XLstartup file on the D: drive and deleted it.

When we ran Excel anew, it started with a fresh Book1, to which we responded with much elation.

However, we didn’t just want to look at the new Book1, so we ventured into new territory; we actually tried a File|Open.

The result was not encouraging.
We got Msgboxes and one dialog window:

Message: Dial-up Networking : Cannot load dialog
Error 623: Cannot find the phonebook entry.

Dialog: Find Fast
(D) Update automatically 3/28/02
© Update automatically 3/28/02

Msgbox: Dr Watson for Windows NT
An application error has occurred and an error log is
being generated.

Such an abundance of information! And I haven’t got the slightest idea what all this means.

We are in need of an oracle.

Hi Charlotte,

Thanks for replying. You know, I finally solved the problem after two days of poking around.

The problem was the the ocx had been deleted when I used an Uninstaller program to clean up a lot of debris on my pc. As a result the common dialog was de-registered.

In Access, to register a library, you go to Tools | References. Not so in VB5. The references are found under Project References. But it wasn’t on the list of available items until you first check it on Project Components. Even after I discovered that, I couldn’t find the comdlg on the toolbar. It was on the toolbar allright, way below the horizon, but because it had no vert. scrollbar you wouldn’t know it was there.

I didn’t find it until I decided to lengthen the toolbar (just out of boredom I guess). After that it was simply a matter of dropping the objects on the form and naming them. The original event code was still there. I had just forgotten about how to register the ocx. You do it once, and forget about it (at your own risk it seems).

Thanks again for your efforts.

Edited by WebGenii on 13-Jul-01 11:59.

I am sorry, but I am not able to help you any further. I cannot duplicate your problems and I can’t find anything in Help. Read Move or Copy sheets.

Try FreeAnswers.com also.
http://www.freeanswers.com/default.asp

Not entirely sure what the problem is.
The links on sheet 2 are missing, but sheet 2(2) works fine.
After I fixed links on sheet 2 and copied the sheet to 2(3), sheet 2(3) also works fine.

Saved the file and re-opened it, and everything is still working as I hoped.

Are we on the same page?

This is an interesting challenge.

First, I tried Legare’s code, but I made a few modifications so I could see it give me some feedback.

Go to the VBA editor and select in the VBA project window, under Microsoft Excel Objects: Sheetn
(Quotes)

Copy and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Worksheets(“Quotes”).Range(“C:C”)) Is Nothing Then
If Target.Value > 10 Then
Target.Font.Bold = True
Else
Target.Font.Bold = False
‘Send the email here
End If
End If
End Sub

This code is for a worksheet Change event. So each time you select a cell and change it, the event code kicks in. To watch it perform, set a breakpoint at the beginning of the Sub.

In this code , you should see Bold in the selected cell go on or off depending on the cell’s value.

Once you understand how this piece works, you still have the task of sending the quote value by email. Which is our next challenge.

Thank you cpod for showing us yet another way to solve a problem.

My code worked, but your formula certainly seems more efficient in a number of ways.

I took your formula apart and learned how it works and it opens new opportunities.

Hi Drk,

Sorry, I must be a bit confused, but I think you want
to compare the typed value in the Combobox to the
combobox list?

Try this code:

Private Function AlreadyListed(ListControl As Control, NewItem As String) As Boolean
Dim i As Integer

AlreadyListed = False

For i = 0 To ListControl.ListCount – 1
If UCase$(ListControl.List(i)) = UCase$(NewItem) Then AlreadyListed = True
Next i
End Function

Private Sub cboOffice_AfterUpdate()
If Not AlreadyListed(cboOffice, cboOffice.Text) Then
‘Do something here
MsgBox “Not in the list”
End If
End Sub

Hi Drk,

Not entirely sure what you’re asking.

>>can I pull this

You can compare new item to combo list and if not found…

>> and put it into another column?

if test is true, you can designate the

Cells(myRow, myCol) = cboBox.Text

And then what?

Here is some code you can try.
The assumptions are that each series will have a name, iow B3 to N3 will be named Series1, B4 to N4 will be Series2 etc.

Sub Calc_Weeks()
Dim NumWeeks As Integer
Dim myCell As Range
Dim myRow As String
Dim FirstCol As Integer
Dim LastCol As Integer
Dim myRangeName As String

myRangeName = InputBox(“Enter Series name”, “Input”)

NumWeeks = 0
FirstCol = 0
LastCol = -1

For Each myCell In Range(myRangeName)
myRow = CStr(myCell.Row)
If myCell.Value 0 Then
If FirstCol = 0 Then
FirstCol = myCell.Column
End If

LastCol = myCell.Column
End If

NumWeeks = (LastCol – FirstCol) + 1
Next myCell
Range(“O” & myRow) = NumWeeks
End Sub

Give it a try and see if it works for you.

The only version that works for me is:

=IF(AND(ISNUMBER(A1),SUM(B1:E1)=0),NA(),””)

Then use Diego’s excellent method.

Try this. Record a macro to setup the header info and then modifiy it to contain the value of a cell on the active sheet. The cell contains the manager’s name, resulting from a lookup in the ManagerList on a separate sheet.

The macro would look something like:

Sub Setup_Header()
Dim myManager As String

myManager = ActiveSheet.Range(“C1”).Value

With ActiveSheet.PageSetup
.PrintTitleRows = “”
.PrintTitleColumns = “”
End With
ActiveSheet.PageSetup.PrintArea = “$B$3:$C$12”
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “&A” & Chr(10) & “UNIV of IL” & Chr(10) & myManager & Chr(10) & “as of &D”
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “”
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Read the .CenterHeader code and see attached xlt to
try it.

Why not put the total line on top of the worksheet, in cell B1 perhaps. That way the location for the links remains the same all the time.

If you don’t want to see the total line, change the font color to the cell’s back color.

The user can just add data to the bottom of the sheet.
Since you already have a macro to find the last row, you can have it also update the total formula(s) on row 1 to include the last row.

Sub Update_Totals()
Dim myLastRow As String

GotoLastCell ‘macro to find last row

myLastRow = CStr(ActiveCell.Row)
Range(“B1”) = “=sum(B3:B” & myLastRow & “)”
End Sub

> I can of course updated by double clicking on the range and then dragging it to include the new data,<

It seems to me that you can do this with a VBA macro.

1. Identify the new data range, first cell/last cell
2. Assign the range (as a string value) to the chart property that needs it.
3. Initiate the action with a button on the sheet(s)

What do you think?

Where can I turn off SubDatasheets – there’s nothing in Tools|Options|Datasheets that I can see relates to Subdatasheets. I unchecked all Name Autocorrect boxes on the General tab. Thanks Charlotte!

I am not certain what is meant by Help being ‘bad’ (as compared to other Helps – I have never found an EASY Help
that does not force me to scan thru reams of unrelated info and many levels of hyperlinks just at a time when I am in a hurry to find an answer to a seemingly easy question – maybe I just don’t have the temperament and/or mental organizing ability to deal with this @%#&).

My problem with Access Help has been (after several installs) that it continues to ask for the CD even though Help loads in a minimized version on the screen. It’s more of an annoyance than a quality issue.

TIA