Posts by Egad

Hi, try this in your custom validation:
(using a1 as your input cell)
=+A1=ROUND(A1,1)

Select all cells in the worksheet and click on Format-Cells-Protection and check the Locked box.
Select the cells to be unprotected and click on Format-Cells-Protection and uncheck the Locked box.
Then click on Tools-Protection-Worksheet to protect the sheet.

A 150k file should open/close quickly with or without macros. Do you have any event macros (workbook_open, workbook_close..) or perhaps links to other files in your workbook?

BTW - I don't want to send MS an error report, ever. Is there a way to turn that feature off?

http://www.ciac.org/ciac/bulletins/m-005.shtml
Apparently there is an option in XP but it's not easy in IE6 (win9x win2x) which requires a registry edit.

Hi guys can you help me with this one? I'm not too familiar with com addins.

It's a question from someone that I referred to Ozgrid, but instead of posting sent me a U2U instead.
I told him how helpful and friendly the board is so our rep is on the line.
Much appreciated, Doug

(I'll let him know about this thread).

--------
Running vba code in Com Addin DLL
Message: I am trying to turn my vba xla addin into a Com Addin DLL , for better code security. I purchased Microsoft XP Developer for the Addin Designer feature. I installed a button on the excel tool bar, with the following code in the Add-in Designer module under Excel.
The COM Addin compiles and installs...the msgboxes work. However, I can't run simple vba subroutines from MyButton such as the one below this code, which creates a new test worksheet in a new workbook. I keep getting a Run-Time error-2147417846 decimal 8001010a hexidecimal. I unstalled Norton AV plug-ins, however, still get the error.

What code/structure changes are needed to do this?

CODE IN ADD-IN DESIGNER***************************

Option Explicit 
Dim oXL As Object 
Dim WithEvents MyButton As Office.CommandBarButton 
Private Sub AddinInstance_onConnection(ByVal Application As Object, _ 
ByVal connectMode As AddInDesignerObjects.ext_ConnectMode, _ 
ByVal addininst As Object, custom() As Variant) 
On Error Resume Next 
MsgBox "my addin start in " & Application.Name 
Set oXL = Application 
Set MyButton = oXL.CommandBars("standard").Controls.Add(1) 
With MyButton 
.caption = "MyButton" 
.Style = msoButtonCaption 
.tag = "my custom button" 
.OnAction = "!<" & addininst.progID & "&gt;" 
.visible = True 
End With 
End Sub 
Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _ 
custom() As Variant) 
On Error Resume Next 
MsgBox "My Addin was disconnected by " & _ 
IIf(RemoveMode = ext_dm_HostShutdown, _ 
"Excel Shutdown. ", "end.user.") 
MyButton.Delete 
Set MyButton = Nothing 
Set oXL = Nothing 
End Sub 
Private Sub MyButton_click(ByVal ctrl As Office.CommandBarButton, _ 
canceldefault As Boolean) 
Call workbook_open 
End Sub 
END OF CODE IN ADDIN DESIGNER**************************** 


CODE IN WORKBOOK_OPEN************************************** 
Sub workbook_open() 
Dim nwb As Workbook 
Set nwb = Workbooks.Add 
Dim paramsheet As String, rawdatasheet As String 
paramsheet = "param" 
rawdatasheet = "RawData" 
nwb.Sheets.Add Type:="Worksheet" 
With ActiveSheet 
.Move After:=Worksheets(Worksheets.Count) 
.Name = paramsheet 
End With 
Sheets.Add Type:="Worksheet" 
With ActiveSheet 
.Move After:=Worksheets(paramsheet) 
.Name = rawdatasheet 
End With 
End Sub

END OF CODE IN WORKBOOK_OPEN**************************

Ok, I just had my first morning cofee.

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
Range("a1:m" & lastCell.Row).Name = "database"

You are close. Try this:
Range("Database").name = Range("a1:m" & lastCell.Row)

edit: ooops. not quite, let me try again.

Yes, you would have to use Ralph's approach, but instead of unprotecting then protecting the sheet again you could use:
activesheet.Protect Password:="password", userinterfaceonly:="True"

at the top, it will allow macros to run without the dangers of temporarily unprotecting the sheet.

Hi try this,

Sub CheckSheet()
On Error GoTo Makesheet
Sheets("ACC").Select
Flag = 1
Makesheet:
If Flag <> 1 Then
Sheets.Add
ActiveSheet.Name = "ACC"
End If
End Sub

Quote

Originally posted by Richie(UK)
Hi Doug,

TDQ = Too Damn Quick :biggrin:

(ie my two fingered typing left me trailing in your wake)

Thanks Richie, I was worried about the "Q". Actually its easier to be TDQ with Derk on holidays.

Quote

Originally posted by _W0lf_
Thanks Ritchie,

Between Egad and your suggestions reckon its pretty much solved.

Thanks Pal

Steve
HTH

Doug = TDQ!

Display More

Err, thanks Steve, I think.
TDQ= ???

You could also use
=SUMIF(A:A,"rabbit",B:B) so that you don't have to specify the starting & ending row numbers.

Vlookup can only return 1 number.
If you would like to add up all the values beside 'rabbits' in column A you could use a sumif:

=SUMIF(A1:A10,"rabbit",B1:B10)

This will add up all the values in column B if 'rabbit ' is in column A. Is this what you are trying to do?

How about a sumproduct approach instead?
=SUMPRODUCT((A1:A100="good")*(B1:B100="x")*C1:C100)

I was in Toronto when it hit. The subways were affected so I had to join the crowds and walk 3 hours out of downtown to get home. 24 hours later the power was still off so we decided to start our trip early. Power was back on in the NE USA so we had no travel problems.
With power off we had quite a view of the evening sky in Toronto. Amost as many stars as I've seen DeepInaHeartofTexas.

Ping Aaron.
Ok, I was there for a week, please explain to me why they call it "The Sunshine State".
:mad:

More info:
The worm corrupted my friend' Windows apps including Explorer, Access, Word, Excel. Many functions including cut & paste operations resulted in error messages & hangs.

A friend & his co-workers also started having problems yesterday with corrupted apps. The bank he works for is infected with the blaster worm. :mad:
You may want to try the http://www.symantec.com/ removal tool.
This may not be the culprit in your case but its worth a look.

Do you need live links? They really slow down calculation times. Would you consider importing the values only and updating them when the macro is run?

After each created link you could add this code:
Cells(ActiveCell.Row, 61).copy
Cells(ActiveCell.Row, 61).PasteSpecial Paste:=xlValues

You should also try changing "activecell.row" to "cell.row" or even just "cell". There is nothing in the code that changes the activecell. The value of "cell" changes in the loop from 1 to 500 but your code does not physicaly move the cursor (the activecell) -and there is no need for excel to physically move the cursor - moving the cursor just slows down the code.