Replies

hi Jefferson, Musical1,

As Musical1 mentioned word 97 explicitly in his/her post, i didn’t provide the visible switch which is not available in word 97, but as an alternative i included the application.update=false and …=true which essentially achieves the same. to be complete, i list the corrected function below, together with a small sub to demonstrate the use of the function :

sub testfunction
if clipboardcontainstable then
msgbox “Table in Clipboard!”
else
msgbox “No Table!”
end if
end sub

function ClipboardContainsTable() as boolean
dim TableDocument as document
dim ScreenUpdate as boolean
screenupdate=application.screenupdating
application.screenupdating=false
set tabledocument=documents.add
with tabledocument
.content.paste
clipboardcontainstable=.tables.count>0
.close savechanges:=wddonotsavechanges
end with
application.screenupdating=screenupdate
end function

after carefully rereading Musical1’s post, i found another way still to test if the cliboard contains a table, which involves reading the caption of the changed control; this would be much faster as it doesn’t involve pasting to a temporary document. i must say i’m not confident of the exact caption of the control, as i’m testing here in dutch, and the caption includes an & which points to the menu’s shortcut key. IIRC, it’s also p for the english version, so the control’s caption will start with the ampersand (&). here’s the modified function which can be used in exactly the same way:

function ClipboardContainsTable() as boolean
clipboardcontainstable= _
ucase(commandbars(“edit”).FindControl(id:=22).Caption) _
=”&PASTE CELL”
end function

greetings,

hi Musical1,

the dataobject will not allow you to make this distinction, maybe there’s an appropriate api functiun to test whether the clipboard contains a table or not, but i don’t know it.
you could however paste to a temporary document and then examine the contents of the document to see if the clipboard contains a table. this is feasable if the tables are not too big:

function ClipboardContainsTable() as boolean
dim TableDocument as document
‘application.screenupdating=true
set tabledocument=documents.add
with tabledocument
.content.paste
clipboardcontainstable=.tables.count>0
.close savechanges:=wddonotsavechanges
end with
‘application.screenupdating=true
end function

also try to play with the options.AutoFormatAsYouTypeApplyTables setting to avoid the column to be converted.

greetings,

hi Awckie,

if you really want to do this in VBA, below you’ll find the sub. but you can achieve the same easily with the vlookup function (check excel’s online help).

Sub MakeList()
Dim CurrentCell As Range
Dim CorrespondingValue As String
    With ActiveSheet
    'loop will stop at the first empty cell in column j
    'if you want to loop to the last cell in j with data in, change
    '.Range("J1").End(xlDown) to
    '.Range("J65536").End(xlup)
        For Each CurrentCell In _
            Range(.Range("J1"), .Range("J1").End(xlDown))
            CorrespondingValue = ""
            Select Case CurrentCell.Text
                Case "K05A"
                    CorrespondingValue = "JA"
                Case "KP60RA"
                    CorrespondingValue = "JP"
                Case "27"
                    CorrespondingValue = "J"
                    'add the other cases
                    'case "xx"
                    'correspondingvalue="y"
                    'case ...
                Case Else
                    CorrespondingValue = "Look up manually"
            End Select
            'comment the case else and the last corresp... line and
            'decomment the following do-loop if you want
            'the user to fill in an inputbox in case
            'he has to do a manual lookup
            'Do
            ' CorrespondingValue = _
            ' InputBox("enter value for " & CurrentCell)
            'Loop Until CorrespondingValue  ""
            CurrentCell.Offset(0, 1) = CorrespondingValue
        Next
    End With
End Sub

greetings, pieter.

hi Awckie,

the code to find the last cell is exactly the same, it uses the end property of the range object with the xldown parameter (which is the same as pressing the end key and then cursor arrow down in the interface)
i also used a string variable to temporarily store the value that needs to be filled in in column K, and then issue only one statement (currentcell.offset(0,1)=correspondingvalue, whereas Sam does this in each case.
i did it this way as it would also optionally allow the inputbox to ask for a value in case the user had to look up manually, should you want to do so.
anyway, the differences are minor.

hth, greetings, pieter.

ps. sorry for the lacking tabs in the code, this was my first post to the list.