Posts by smozgur

Hi Rod,

Try;

Menu: Tools_Options
Click:Edit Tab
Select:Allow cell drag an drop

Same thing for macros :

application.CellDragAndDrop=True

I hope it helps.

Use a Group Box (also known as Frame) to create your different option groups.

(It is next to Button control on Forms toolbar.)

Yes, you can.

Assuming you have recipient names in A2 to A10 cells in Sheet1.

ActiveWorkbook.SendMail Sheet1.Range("A2:A10")

I hope it helps.

Hi Bernz,

Assuming:
Sheet1 has unique data
Sheet1 A column is the unique record number colum
Sheet2 has details for unique IDs in Sheet1
Sheet2 A column is the unique record number colum

Open VBA (Alt+F11) and insert new module (Insert_Module) then paste the code below in this module.

'ID is the unique record number
Sub DeleteData(ID As Long)


Dim sht As Worksheet
Dim rng As Range
    
    'First deleting sheet1 data
    'Set worksheet object reference
    Set sht = Sheet1
    'Set range for the lookup value
    Set rng = sht.Cells.Find(ID, sht.Range("A1"))
    'Run the code in loop until there is no more ID in worksheet
    'This is not necessary in Sheet1 since it is unique actually
    Do Until rng Is Nothing
        'Delete row for previously found ID
        sht.Rows(rng.Row).EntireRow.Delete
        'Search again
        Set rng = sht.Cells.FindNext
    Loop
    
    'Deleting sheet2 data
    'Set worksheet object reference
    Set sht = Sheet2
    'Find first row with ID
    Set rng = sht.Cells.Find(ID, sht.Range("A1"))
    'This code will work here for more than one row to find next one
    Do Until rng Is Nothing
        sht.Rows(rng.Row).EntireRow.Delete
        Set rng = sht.Cells.FindNext
    Loop
    
End Sub

Now you can call this procedure in another procedure and fire it with a command button assignment:

Say you want to put a command button on one of the worksheets and want to be asked for the ID to be deleted.

Goto same module and paste the code below:

Sub ButtonMacro()
Dim tmpID As String
    'Ask for ID
    tmpID = InputBox("Enter ID to delete transactions data.", "Enter ID")
    'Check if ID is valid, otherwise warn user
    If Trim(tmpID) = "" Then Exit Sub
    If Not IsNumeric(tmpID) Then GoTo invalidID
    'Run the main procedure
    Call DeleteData(CLng(tmpID))
ExitProc:
    Exit Sub
invalidID:
    'Warn user
    MsgBox "Invalid ID. Please try again.", vbOKOnly + vbExclamation, "Error"
End Sub

And create a button on any worksheet (and make it Don't Move or Size with Cells if you will use it in Sheet1 or Sheet2) and just assign the ButtonMacro macro to this button.

I hope it helps.

Quote

Originally posted by Chris Davison
if you only want to trap an #N/A error, you might be better off actually doing an =COUNTIF first rather than using the =ISERROR....

=ISERROR will catch ALL errors...... which may be disastrous if you subsequently think you've brought back all relevant info

try something along the lines of :

=IF(COUNTIF(A2:A7,C2)=0,"Suat",VLOOKUP(C2,A2:B7,2,0))

(it also does away with the need to perform the VLOOKUP twice in the case of a "kosher" match, so should speed up your worksheet if you've noticed any performance degredations)

Sorry Suat - I borrowed your example :bsmile:

Display More

Better solution, Chris

BTW, You are definetely right about performance issue.

=IF(ISERROR(VLOOKUP(C2,A2:B7,2,FALSE)),"Suat",VLOOKUP(C2,A2:B7,2,FALSE))

Assuming lookup table is A2:B7 range.

I hope it helps.

I don't know any option or direct way in Excel but this may help:

http://www.vbrad.com/pf.asp?p=source/src_xl_nb_addin.htm

Quote

Originally posted by Anonymous
Thank you very much.
What are the keystrokes "^+{2}" Ctrl-Shift-2 ?
I changed them to "^{'}" , Ctrl-Aposthrophe

And it works great.
Thank you again

Yep, "^+{2}" = Ctrl-Shift-2 and your one is the same and better for reading.

You're welcome.

This may help :

ControlName is the control name of that field on form.

Private Sub Command1_Click()
    Me.ControlName.SetFocus
    SendKeys "^+{2}", True
End Sub

Regards.

You're welcome, Tony.

I assume your procedure is a function and also in a module like below :

Function MyFunc(myVal As Long)
Dim i As Integer
For i = 1 To Len(myVal)
MyFunc = MyFunc + Val(Mid(myVal, i, 1))
Next i
End Function

Now just goto B10 and write :

=MyFunc(A10)

I hope this helps.

Hi, Dennis

Hi,

Have you checked Black and White option for that worksheet ?

Click File_Page Setup and select Sheet Tab then look for the Black and White checkbox is checked in Print section. If checked then uncheck it and click ok.

I hope this helps.

You're welcome, Sunrise.

Hi,

This code will update Sheet1's center header by changing A1 cell.

'Code goes to Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheet1.PageSetup.CenterHeader = Sheet1.Range("A1").Value
End If
End Sub

Code line also can be changed like below to format font.

Sheet1.PageSetup.CenterHeader = "&""Arial,Bold""&9" & Sheet1.Range("A1").Value

You can also modify this code to set same font format with the source cell by using parameters for fontname, bold and size values.

I hope this helps.