[AccessD] Excel Object - Can You Tell What Row Or Column Is Clicked
James Button
jamesbutton at blueyonder.co.uk
Fri Jun 24 09:50:57 CDT 2016
You may find some of the following code scraps useful
DefaultwindowCapn = Windows(1).Caption
DefaultLocation = CurDir()
Defaultfile = ActiveWorkbook.FullName
Defaultbook = ActiveWorkbook.Name
DefaultSheet = ActiveSheet.Name
Defaultversion = Application.Version
DefaultPath = Application.DefaultFilePath
DefaultOS = Application.OperatingSystem
DefaultName = Application.Name
Defaultrow = ActiveCell.Row
Defaultcol = ActiveCell.Column
wbformat = ActiveWorkbook.FileFormat
wbreadonly = ActiveWorkbook.ReadOnly
wbsaved = ActiveWorkbook.Saved
wbreserver = ActiveWorkbook.WriteReservedBy
wbuser = Application.UserName
DefaultwindowCapn = Windows(1).Caption
' Then to put the session back to the earlier window (multiple views) follow any
file or sheet closure with
Windows(DefaultwindowCapn).Activate
wbsaved = ActiveWorkbook.Saved
If wbsaved <> True Then
If wcreadonly <> False _
Or wcuser <> wcreserver Then
If wcformat <> xlNormal And _
wcformat <> 50 And _
wcformat <> 51 And _
wcformat <> 52 And _
wcformat <> 56 Then _
Ab = MsgBox("Control File has one or more unexpected attributes
which may cause problems" _
& vbCrLf & " Format of file is """ & wbformat & """,
expected Normal code ""-4143"" " _
& vbCrLf & " ReadOnly Mode of file is """ & wbreadonly &
""", expected False" _
& vbCrLf & " Current Updater of file is """ & wbreserver
_
& """, expected current user """ & wbuser &
"""" _
& vbCrLf _
& vbCrLf & "Select ""OK"" to continue with attempted
save of file and rest of processing." _
& vbCrLf _
& vbCrLf & " Format codes within Excel 97 to 2003 are "
_
& vbCrLf & " -4143=Normal (XLS) -4158=text 2=SYLK
4=WKS 5=WK1 6=CSV 7=DBF2 8=DBF3" _
& vbCrLf & " 9=DIF 11=DBF4 14=WJ2WD1 15=WK3
16=Excel2 17=Template 18=Addin" _
& vbCrLf & " 19=TextMac 20=TextWindows
21=TextMSDOS 22=CSVMac 23=CSVWindows" _
& vbCrLf & " 24=CSVMSDOS 25=IntlMacro
26=IntlAddin 27=Excel2FarEast" _
& vbCrLf & " 28=Works2FarEast 29=Excel3
30=WK1FMT 31=WK1ALL 32=WK3FM3 33=Excel4" _
& vbCrLf & " 34=WQ1 35=Excel4Workbook
36=TextPrinter" _
& vbCrLf & " Format codes within Excel 2007 are " _
& vbCrLf & " 50=xlsb 51=xlsx (no macros)
52=xlsm 56=xls", _
Buttons:=vbOKCancel + vbCritical + vbDefaultButton1, _
Title:=DefaultProcname & " File status")
If Ab = vbCancel Then
Exit Sub
End If
End If
ActiveWorkbook.Save
End If
And - for basic debugging
If Infos Then
Ab = MsgBox("This macro has ended ", _
Title:=DefaultsubProcname & " has ended")
End If
NocloseCtrlsn = False
For Each Wrkbk In Workbooks()
If Wrkbk.FullName = Controldata Then
NocloseCtrlfn = True
ElseIf Wrkbk.Name = controlfile Then
NocloseCtrlsn = True
NocloseCtrlfn = True
Ab = MsgBox("Cannot access expected ""Control"" workbook" _
& vbCrLf & "This session of Excel appears to already
have a ""Control"" file named " _
& vbCrLf & " " & Wrkbk.FullName & "\" _
& vbCrLf & " open." _
& vbCrLf & "This file has the same name as the
specified control file," _
& vbCrLf & " but is in a different directory from
that specified:- " _
& vbCrLf & " " & controldiry & "\" _
& vbCrLf & "As Excell will only allow 1 file of each
short name to be open" _
& " at a time," _
& vbCrLf & " the file that is already open will be
used as is by the macro" _
& " and neither saved or closed" _
& vbCrLf & vbCrLf & " Either select ""OK"" to
continue, using the open file," _
& " or ""Cancel"" the process", _
Buttons:=vbOKCancel + vbCritical + vbDefaultButton1,
_
Title:=DefaultProcname & "Alternate Controls open")
If Ab = vbCancel Then
Exit Sub
End If
End If
Next Wrkbk
'
' If file is not already open then open and activate it otherwise If
going to update file say it will be saved twice, activate it and save it If
not going to be updated, warn that it will be used as is and just activate
it
If NocloseCtrlfn = False Then
Workbooks.Open Filename:=Controldata, Updatelinks:=0,
ReadOnly:=ReadOnlyControlFile
Workbooks(controlfile).Activate
Else
If ReadOnlyControlFile = True Then
Ab = MsgBox("This session of Excel appears to already have the
""Control"" file named " _
& vbCrLf & Controldata _
& vbCrLf & " open, it will be used as is by the macro
and neither saved or closed" _
& vbCrLf & vbCrLf & "If that is acceptable then select
""OK"" - " _
& " otherwise select ""Cancel"" to abandon this
process", _
Buttons:=vbOKCancel + vbInformation +
vbDefaultButton1, _
Title:=DefaultProcname & "Controls already in use")
If Ab = vbCancel Then
Exit Sub
End If
Workbooks(controlfile).Activate
Else
' test if we can save the workbook, - note that we are to update it, so we
should save it
Workbooks(controlfile).Activate
wcformat = ActiveWorkbook.FileFormat
wcreadonly = ActiveWorkbook.ReadOnly
wcuser = Application.UserName
wcreserver = ActiveWorkbook.WriteReservedBy
If wcreadonly <> False _
Or wcuser <> wcreserver Then
If wcformat <> xlNormal And _
wcformat <> 50 And _
wcformat <> 51 And _
wcformat <> 52 And _
wcformat <> 56 Then _
Ab = MsgBox("Control File has one or more unexpected attributes
which may cause problems" _
& vbCrLf & " Format of file is """ & wbformat & """,
expected Normal code ""-4143"" " _
& vbCrLf & " ReadOnly Mode of file is """ & wbreadonly &
""", expected False" _
& vbCrLf & " Current Updater of file is """ & wbreserver
_
& """, expected current user """ & wbuser &
"""" _
& vbCrLf _
& vbCrLf & "Select ""OK"" to continue with attempted
save of file and rest of processing." _
& vbCrLf _
& vbCrLf & " Format codes within Excel 97 to 2003 are "
_
& vbCrLf & " -4143=Normal (XLS) -4158=text 2=SYLK
4=WKS 5=WK1 6=CSV 7=DBF2 8=DBF3" _
& vbCrLf & " 9=DIF 11=DBF4 14=WJ2WD1 15=WK3
16=Excel2 17=Template 18=Addin" _
& vbCrLf & " 19=TextMac 20=TextWindows
21=TextMSDOS 22=CSVMac 23=CSVWindows" _
& vbCrLf & " 24=CSVMSDOS 25=IntlMacro
26=IntlAddin 27=Excel2FarEast" _
& vbCrLf & " 28=Works2FarEast 29=Excel3
30=WK1FMT 31=WK1ALL 32=WK3FM3 33=Excel4" _
& vbCrLf & " 34=WQ1 35=Excel4Workbook
36=TextPrinter" _
& vbCrLf & " Format codes within Excel 2007 are " _
& vbCrLf & " 50=xlsb 51=xlsx (no macros)
52=xlsm 56=xls", _
Buttons:=vbOKCancel + vbCritical + vbDefaultButton1, _
Title:=DefaultProcname & " File status")
If Ab = vbCancel Then
Exit Sub
End If
End If
' tested if we should be able to save the workbook, - as we are to update it,
so warn and save it
Ab = MsgBox("This session of Excel appears to already have the
""Control"" file named " _
& vbCrLf & Controldata _
& vbCrLf & " open, it will now be saved, used by the
macro and then re-saved, not closed" _
& vbCrLf & vbCrLf & "If that is acceptable then select
""OK"" - " _
& " otherwise select ""Cancel"" to abandon this
process", _
Buttons:=vbOKCancel + vbInformation +
vbDefaultButton1, _
Title:=DefaultProcname & "Controls already open")
If Ab = vbCancel Then
Exit Sub
End If
Workbooks(controlfile).Activate
ActiveWorkbook.Save
End If
End If
' SaveChanges=True if opened with READONLY=False
' (indicates that Serial number is to be incremented)
' Now go and look for the control values
Lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
More information about the AccessD
mailing list