Posts by Jack in the UK

The code should be located in "ThisWorkbook" and under "Workbook" and "Open"

Something like

Private Sub Workbook_Open()
Application.OnTime TimeValue("11:00:00"), "ShowMessageMidday"
End Sub

jiuk

Long time since I coded in Outlook or linked up outlook, my feelings here are:

You need to pull back the object before send. Once sent, you cannot touch the email or its containers unless outside of this procedure (I do not have Outlook to test)

Link required: olkEm

Try this code,

olkEm.DeleteAfterSubmit = True

Vert tardy code untested so please check on a copy of your workbook and make sure you test to your own email account only!!
Edit email address once tested and working correctly

Not sure of how this code will work out, but worth a try

jiuk

With olkEm
 .To =
"atul.jadhav@netafim.com"
 .CC =
"atul.jadhav0224@gmail.com"
 .BCC = ""
 .Subject = "File
opened"
 .Body = strbody
 .DeleteAfterSubmit =
True
 .Send
 End With

‘// jiuk - changed from here
‘// jiuk – add attachments here I believe … Must be last in
the processes
‘// jiuk – code something like
‘// jiuk - olkEm.Attachments.Add ***** filepath-HERE”

olkEm.send

‘// jiuk – now delete the email
olkEm. DeleteAfterSubmit = True
Goto theEnd:

theEND:
 On Error GoTo 0
 Set olkEm = Nothing
 Set olkObj = Nothing
End Sub

Hi thanks for the reply, try at add this code

jiuk

Sub Email_v2()

' // jiuk - Add wait code here
Application.Wait (Now + TimeValue("0:00:03"))
GoTo theEND_2:

'// jiuk - this is why in all Jacks code there is such a line code theEND: to wrap things up
'// jiuk - the user will have to click OK
theEND_2:
MsgBox "Email sent"
Exit Sub
End Sub

scanned document are as such pictures not anything else, you will need a very good quality OCR application to convert to any other file format

Going to Excel by the conversion method, will never quite look like you expect and the output will need lots of work and correction unless it is raw text in columns

Normally the data files is converted to a picture i.e. PDF which works well

jiuk

If this is in VBA | Excel and the codes all working as you want why edit | change the code

Jack would add a pop up box to at the end of the procedure i.e. a message box

sub Email()
code
code
code 
.
.

'// jiuk  - this is why in all Jacks code there is such a line code theEND: to wrap things up
'// jiuk - the user will have to click OK
theEND:
Msgbox "Email sent"
exit sub
end sub

Also use something like this code to kind of pause the code i.e. delay the message box being seen

Add this code above the msgbox line code and under theEND:

Application.Wait (Now + TimeValue("0:00:03"))

Some ideas to try - remember to test on a copy of your workbook just in case

jiuk

That’s true Raina, Dave helpped millions of people and still does!
There is a lot Jack could give away about the Hacks Books, ideas how it came about and input from others - Dave, well (with your support I remember well) just did it, against the odds and still stands today

Jack has loads of memory’s and history - the question should be was OzGrid to really to be named OzGrid the answer to this day is most probably do not know

The answer is not at all – so what happened?

jiuk

Always test on copy of your workbook as changes back or undo are not always possible. Add this code to a workbook standard module and run from the VBIDE or add a button as normal

The code basically add colour fill to 6 cells up from the active cell, you may need erro handling

You code is out of sequence, that said the loop will not do anything you can see once it has run

>>>>

.Offset(0 + i, 0).Interior.Color = RGB(0, 0, 275 - a)

'// Needs to be inside the loop for the cells to be filled dark blue

Something to read that might explain a little more

Sub COLOUR_S()

Dim i As Integer

Dim myMIN As Long
Dim myMAX As Long
Dim myVAR As Long

myVAR = 20
myMIN = 1
myMAX = 6
For i = myMIN To myMAX Step 1
With ActiveCell
.Offset(0 + i, 0).Interior.Color = _
RGB(0, 0, 275 - myVAR)
End With
Next i

theEND:
'// jiuk - nothing
Exit Sub

End Sub

Something to play with - test on a copy workbook and add to the sheet module etc

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'// jiuk - Edit as required
'// jiuk - Format(Now, "mm/dd/yyyy HH:mm:ss")

'Dim D As Long, T
'If Target.Column <> 1 Then Exit Sub
'D = Date
'T = Time
'Cells(Target.Row, 3).Offset(1) = Application.WorksheetFunction.Text(D, "dd/mm/yyyy") & " " & T

Dim D As Long
Dim T

If Intersect(Target, Range("A:A")) Is Nothing Then
On Error Resume Next
GoTo theEND

Else
'jiuk - edit as required HH mm ss etc
Cells(Target.Row, 3).Offset(1).Value = Format(Now, "dd/mm/yyyy HH:mm:ss")
End If

theEND:
Exit Sub

End Sub

if 365 is possible its a bit or a round-about, thou should work out ok from them that need Excel over A.N.Other

Depends on your organisation, many do not code so makes little difference and most don’t use heavy Excel. Be a steep learn 5000+ is a mission waiting

Compatibility needs a keen eye! i.e. existing XLS vs libra

jiuk

Only use merged cells for titles, nothing else as are painful at best. Never use merged cells in reports or data tables as will always cause problems

As advised user center cells align, left right text align etc etc and only in titles

Note of warming even in titles VBA can get picky so again, highlight caution with use

jiuk

If this is a small outfit, sure why not, Libre Office, will take XLs files to a point, and there will be some process or conversions

Codes are toast, regardless what anyone says. Much work will be required either way - so this is a big balance

Large enterprise organisations, will never touch anything outside office for core applications i.e. 10,000 users and more its not possible, just sit and use older versions of Office 2010 or 2013 on Win 7 or 10 and alls well

Maybe the best approach is to alpha test this once spun up as a project and have 2-5% or workforce test and see the results and impact, leaving office as a back stop

Again or my workplace this is impossible, the largest employer in Europe and one of the larges in the world, its an impossible change

jiuk

Thanks for the reply Glen, wonderful you have your copy of Excel Hacks,

As you get to know Excel you find you know less than you did before and develop your style which is important. As long as the task gets done and works as you want then all is good

Hacks are great and all the tricks you can learn makes things fun and amazing when others see or you show off you latest find

jiuk

Just for fun, the very first version of Excel Hacks, I worked on with Dave, something we discussed long before Oz hit the world as Dave had brilliant readership to his newsletter and strong reader base

before put the shelf, Dave posted a signed copy to RoyUK and myself (think it was RoyUK)


There were a couple of named guys that added Hacks are credited for their work in the publication, I even found a place where the electronic version was downloadable, Dave was not bothered by this and said ah… let ‘em have it enjoy

I still have the copy on my shelf, and have kept it from Day one, sadly Dave did not sign it to Jack, but the words read “that’s for all your help” signed Dave Hawley. I guess it one of many give away to launch the book and was given to a few, still a nice thought and posted from Oz to London was very kind

jiuk

Re: Camera tool don't work properly

assuming you are not using the tool in a dynamic flavor, so its its static for now, copy past in to MSPaint - cut selection and paste back

Hardly satisfactory agreed, that is what I do thou, the tools good and powerful gizmo

Re: Focus the cell during spell checking using VBA

try this old old code from me - nearlyu 10 years back

jiuk

Option Explicit
 


 
Sub JR_SpellA()
     
     ' ----------------------------------------------------------------------
     ' Module Name : JR_SpellA
     ' DateTime    : 20 Aug 2005 - 22:06
     ' Author      : Jack in the UK
     ' Website     : [url]www.excel-it.com[/url]
     ' Purpose     : Witten for [url]www.OzGrid.com[/url]
     ' OzGrid Feed : [url]http://www.ozgrid.com/forum/showthread.php?t=38501[/url]
     ' ----------------------------------------------------------------------
     
    Dim rRng As Excel.Range
    Set rRng = ActiveSheet.UsedRange
    rRng.SpecialCells(xlCellTypeConstants, 22).Select
    Selection.CheckSpelling SpellLang:=2057
    Set rRng = Nothing
End Sub

Re: 26th January 2003 decade of help

RoyUK ozGrid join date
26th January 2003

Jack in the UK join date
26th January 2003

Cheers Dave full credit, congrats to everyone

Re: Simple Data Validation Causing Corrupt File

Sounds tio me like you have some sort of conflict in the DV

Im guessing look at the 255 character limitation

Re: Excel 2010 issue after install windows 7

You can use different colourrs to display the gridlines

File
Options
Advanced
Display options for this worksheet

Show gridlines must be checked
Choose a different dark colour shade from the Gridline Colour dropdown color palette

See if that helps

Re: Change Sheet Tab Name & Still Be Able To Reference It In VBA Macro Code

Use the sheet name not the tab name then you call it via code and its always the same