VBA to colour table cells and add values when a function key is pr

I have a VBA application that teachers can use to mark assignments by placing 
their cursor in a particular cell in a table then pressing function keys. 
Each row in the table corresponds to a specific performance standard (and 
contains a mark) and the last column is used to store the total marks. If the 
teacher places the cursor in a row and presses F6 then that row is coloured 
and the mark is inserted into the last colum in that that row. You can see a 
screen image at 
http://emarking-assistant.baker-evans.com/screen_image.htm

Currently the VBA does not need to know what row or cell the cursor is in 
and it uses the following to move around the table or make selections
  Selection.EndKey Unit:=wdRow, Extend:=True 
  Selection.HomeKey Unit:=wdRow
  Selection.MoveRight Unit:=wdCell, count:=2

The next version of the application will be a little more complicated and 
will require VBA to know what cell the cursor is in so it can put the correct 
mark in the corresponding total cell.

How can VBA know what cell the cursor is in when a function key is pressed? 
And then move to the 5 column in that row and insert a value into it.

Thanks in advance for any assistance,
Peter Evans

0
Utf
12/27/2009 1:29:01 PM
word.vba.general 1023 articles. 1 followers. Follow

3 Replies
1599 Views

Similar Articles

[PageSpeed] 41

You would assign the function key to something like this:

Sub ScratchMaco()
Dim oColS As String, oRowS As String
If Selection.Information(wdWithInTable) And Selection.Cells.Count < 2 Then
  With Selection
    oColS = .Information(wdStartOfRangeColumnNumber)
    oRowS = .Information(wdStartOfRangeRowNumber)
    Selection.Tables(1).Cell(oRowS, 5).Range.Text = "The selection is at 
column: " & oColS & " row: " & oRowS & "."
  End With
Else
  MsgBox "Please ensure the selection is contained withing a single table 
cell."
End If
End Sub
"Peter" <Peter@discussions.microsoft.com> wrote in message 
news:083BE2C8-7FDB-4695-AF7B-6F24E62171E4@microsoft.com...
>I have a VBA application that teachers can use to mark assignments by 
>placing
> their cursor in a particular cell in a table then pressing function keys.
> Each row in the table corresponds to a specific performance standard (and
> contains a mark) and the last column is used to store the total marks. If 
> the
> teacher places the cursor in a row and presses F6 then that row is 
> coloured
> and the mark is inserted into the last colum in that that row. You can see 
> a
> screen image at
> http://emarking-assistant.baker-evans.com/screen_image.htm
>
> Currently the VBA does not need to know what row or cell the cursor is in
> and it uses the following to move around the table or make selections
>  Selection.EndKey Unit:=wdRow, Extend:=True
>  Selection.HomeKey Unit:=wdRow
>  Selection.MoveRight Unit:=wdCell, count:=2
>
> The next version of the application will be a little more complicated and
> will require VBA to know what cell the cursor is in so it can put the 
> correct
> mark in the corresponding total cell.
>
> How can VBA know what cell the cursor is in when a function key is 
> pressed?
> And then move to the 5 column in that row and insert a value into it.
>
> Thanks in advance for any assistance,
> Peter Evans
> 


0
Greg
12/27/2009 2:03:43 PM
Greg,

Thanks. The info you provided has been very helpful. In the current version 
criteria are listed in rows and standards in columns from poor to excellent. 
The heading for the standards can be white or coloured. If coloured then that 
colour is used to highlight the cell and the mark (if not then brite green is 
used). e.g.

if oTbl.Cell(1, oColS).Shading.BackgroundPatternColorIndex = wdWhite Then
   oTbl.Cell(oRowS, oColS).Shading.BackgroundPatternColorIndex = wdBrightGreen
Else
   oTbl.Cell(oRowS, oColS).Shading.BackgroundPatternColor = _
       oTbl.Cell(1, oColS).Shading.BackgroundPatternColor
End If

Below is a link to a screen image
http://emarking-assistant.baker-evans.com/tempdropbox/markingRubric.gif

Function keys increment or decrement the mark if there is a mark range. I 
would like to vary the brighness of the shading based on the incrementing. I 
tried usinng
   Selection.Shading.Texture
but that produced only a colur with black of white dots.

How can I vary the colour in increments around a 
Selection.Shading.BackgroundPatternColor
or 
Selection.Shading.BackgroundPatternColorIndex

As always thanks in advance for any assistance,
Peter Evans

"Greg Maxey" wrote:

> You would assign the function key to something like this:
> 
> Sub ScratchMaco()
> Dim oColS As String, oRowS As String
> If Selection.Information(wdWithInTable) And Selection.Cells.Count < 2 Then
>   With Selection
>     oColS = .Information(wdStartOfRangeColumnNumber)
>     oRowS = .Information(wdStartOfRangeRowNumber)
>     Selection.Tables(1).Cell(oRowS, 5).Range.Text = "The selection is at 
> column: " & oColS & " row: " & oRowS & "."
>   End With
> Else
>   MsgBox "Please ensure the selection is contained withing a single table 
> cell."
> End If
> End Sub
> "Peter" <Peter@discussions.microsoft.com> wrote in message 
> news:083BE2C8-7FDB-4695-AF7B-6F24E62171E4@microsoft.com...
> >I have a VBA application that teachers can use to mark assignments by 
> >placing
> > their cursor in a particular cell in a table then pressing function keys.
> > Each row in the table corresponds to a specific performance standard (and
> > contains a mark) and the last column is used to store the total marks. If 
> > the
> > teacher places the cursor in a row and presses F6 then that row is 
> > coloured
> > and the mark is inserted into the last colum in that that row. You can see 
> > a
> > screen image at
> > http://emarking-assistant.baker-evans.com/screen_image.htm
> >
> > Currently the VBA does not need to know what row or cell the cursor is in
> > and it uses the following to move around the table or make selections
> >  Selection.EndKey Unit:=wdRow, Extend:=True
> >  Selection.HomeKey Unit:=wdRow
> >  Selection.MoveRight Unit:=wdCell, count:=2
> >
> > The next version of the application will be a little more complicated and
> > will require VBA to know what cell the cursor is in so it can put the 
> > correct
> > mark in the corresponding total cell.
> >
> > How can VBA know what cell the cursor is in when a function key is 
> > pressed?
> > And then move to the 5 column in that row and insert a value into it.
> >
> > Thanks in advance for any assistance,
> > Peter Evans
> > 
> 
> 
> .
> 
0
Utf
12/30/2009 3:42:01 AM
Peter,

You could use RBG values.  Something like this:

Sub ScratchMaco()
Dim i As Long 'Shading factor
i = 45 * InputBox("Enter a value grade 0 to 5")
Selection.Cells(1).Range.Shading.BackgroundPatternColor = RGB(255, i + 0, i 
+ 0)
End Sub

A zero entry applies solid red.

Peter wrote:
> Greg,
>
> Thanks. The info you provided has been very helpful. In the current
> version criteria are listed in rows and standards in columns from
> poor to excellent. The heading for the standards can be white or
> coloured. If coloured then that colour is used to highlight the cell
> and the mark (if not then brite green is used). e.g.
>
> if oTbl.Cell(1, oColS).Shading.BackgroundPatternColorIndex = wdWhite
>   Then oTbl.Cell(oRowS, oColS).Shading.BackgroundPatternColorIndex =
> wdBrightGreen Else
>   oTbl.Cell(oRowS, oColS).Shading.BackgroundPatternColor = _
>       oTbl.Cell(1, oColS).Shading.BackgroundPatternColor
> End If
>
> Below is a link to a screen image
> http://emarking-assistant.baker-evans.com/tempdropbox/markingRubric.gif
>
> Function keys increment or decrement the mark if there is a mark
> range. I would like to vary the brighness of the shading based on the
> incrementing. I tried usinng
>   Selection.Shading.Texture
> but that produced only a colur with black of white dots.
>
> How can I vary the colour in increments around a
> Selection.Shading.BackgroundPatternColor
> or
> Selection.Shading.BackgroundPatternColorIndex
>
> As always thanks in advance for any assistance,
> Peter Evans
>
> "Greg Maxey" wrote:
>
>> You would assign the function key to something like this:
>>
>> Sub ScratchMaco()
>> Dim oColS As String, oRowS As String
>> If Selection.Information(wdWithInTable) And Selection.Cells.Count <
>>   2 Then With Selection
>>     oColS = .Information(wdStartOfRangeColumnNumber)
>>     oRowS = .Information(wdStartOfRangeRowNumber)
>>     Selection.Tables(1).Cell(oRowS, 5).Range.Text = "The selection
>> is at column: " & oColS & " row: " & oRowS & "."
>>   End With
>> Else
>>   MsgBox "Please ensure the selection is contained withing a single
>> table cell."
>> End If
>> End Sub
>> "Peter" <Peter@discussions.microsoft.com> wrote in message
>> news:083BE2C8-7FDB-4695-AF7B-6F24E62171E4@microsoft.com...
>>> I have a VBA application that teachers can use to mark assignments
>>> by placing
>>> their cursor in a particular cell in a table then pressing function
>>> keys. Each row in the table corresponds to a specific performance
>>> standard (and contains a mark) and the last column is used to store
>>> the total marks. If the
>>> teacher places the cursor in a row and presses F6 then that row is
>>> coloured
>>> and the mark is inserted into the last colum in that that row. You
>>> can see a
>>> screen image at
>>> http://emarking-assistant.baker-evans.com/screen_image.htm
>>>
>>> Currently the VBA does not need to know what row or cell the cursor
>>> is in and it uses the following to move around the table or make
>>>  selections Selection.EndKey Unit:=wdRow, Extend:=True
>>>  Selection.HomeKey Unit:=wdRow
>>>  Selection.MoveRight Unit:=wdCell, count:=2
>>>
>>> The next version of the application will be a little more
>>> complicated and will require VBA to know what cell the cursor is in
>>> so it can put the correct
>>> mark in the corresponding total cell.
>>>
>>> How can VBA know what cell the cursor is in when a function key is
>>> pressed?
>>> And then move to the 5 column in that row and insert a value into
>>> it.
>>>
>>> Thanks in advance for any assistance,
>>> Peter Evans
>>>
>>
>>
>> . 


0
Greg
12/30/2009 12:35:03 PM
Reply:

Similar Artilces:

VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...

Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...

Colour Grouping of Columns
Excel 97 Is there a way to colour the columns in a chart depending on their numerical range. For example: Say we have a series of values from 0 through 100 represented in the column chart. I would like 0-25 to be one colour, 26-50 to be another, etc etc. Is this possible? Any advice gratefully appreciated. WSF WSF, Yes, you can do so by setting up various ranges with IF statements and multiple culumns. Please see Jon Peltier's discussions on conditional charting: http://www.peltiertech.com/Excel/Charts/ConditionalChart1.html http://www.peltiertech.com/Excel/Charts/ConditionalC...

"Enter Parameter Value"
[Using Access 2007] I've changed two fields in a table and subsequently deleted those original items from the corresponding form and have tried to insert the newly updated fields. Now, I am receiving "Enter Parameter Value" when I try to open the form. Also, when I try to insert the updated fields into the form, in the "Fields available for this view," the fields I deleted are shown in the list at the bottom and slightly indented to the left. I've also deleted two queries that I had started that I thought might be interfering and generating the "para...

GL10001 table Issue
Hi, This is the 2nd time within 6-7 months period, we experienced that GL10001 detail file getting truncated. All Header GL10000 records were intact. Do not know what causing it. I think its getting cleared while posting the batches. I cheked the audit log sy05000 even though it says the General Enry batch XXXXXXXX has finished posting but no records were found in GL20000. Is there any way to find out what causing it or where to search for possible log of any kind. Appreciate your help. Thanks Hi, This is intermittently occuring, with the GL Header intact and GL...

Add sequences of positive then negative numbers
I have a long list of random positive and negative numbers in column A. Id like to be able to add sequences of positive then negative numbers as they occur. For example... in the list 5,-1,-5,-3,2,8,-1,-3,9,7,5,11,-5,6,5 ... i would add the positive numbers until the next number in the list was negative, then add the negative numbers till the next number was positive, etc. The answers for the above list would be 5, -9, 10 , -4, 21, -5, 11 Any ideas ? -- judoist ------------------------------------------------------------------------ judoist's Profile: http://www.excelforum.com/mem...

Restoring Junk E-Mail Add Sender to Blocked Senders List Pop-Up Confirmation Box
Hi, When Outlook 2003 is first installed, and you right-click on an email and then select junk email: add sender to blocked senders list from the context menu, a pop-up confirmation box appears that simply confirms that the sender has been added to the blocked senders list. There is a checkbox that you can check here that will prevent this pop-up box from appearing in the future, and I accidentally checked it. How do I get this pop-up box to re-appear? Any help is greatly appreciated! Thanks - marc ...

Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...

VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

Publishing a pivot table
I have a friend who wants to publish a pivot table. We go throught the Excel dialogs to publish and 'Add interacvtivity' with 'PivotTable functionality' but he only gets the table showing with no data. There is also somebody else with a similar problem where the Published web page just contains a placeholder within IE (i.e. the pivot table does not appear in IE). Can anybody help. One alternative is to use the Office Web Components available as a free download from the microsoft office download site. -- Regards, Alvin Bruney [MVP ASP.NET] [Shameless Author plug] The M...

Table Combination with Sorting
I have a workbook with two sheets with customer names and receivable balances from two different divisions (the customer names and balances change daily). Some of the customers are the same on both sheets. I would like to create a third sheet that will list all of receivable balances from both sheets and combine the balances of any customers that are the same in both sheets and then have the balances sorted from highest to lowest. The result in the third sheet will be linked to another report. I currently do this manually - is there a better way using functions? Would pivot tables ...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

How do I link one person to another in the same table?
I have a table of Contacts. Some of my contacts know other contacts, and I would like to indicate that in their record. However, for Data Type, I cannot create a drop down list of contacts from within the table to choose from. Any idea how to to this? Or, if this is not possible, can you suggest another way to indicate "who knows whom"? Your contact table ought to look like: TblContact ContactID FirstName LastName etc Then you need am acquaintenances table: TblAcquaintenance AcquaintenanceID ContactID (identifies a Contact) AcquaintedContactID (Ident...

vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...

View Pivot Table Source data as a Data Table
Hello kind & helpful people, I am using Excel 2007 I have a pivot table and I want to be able to look at all of the data that it represents at once. That is, I want to see the original data table that was used to create the Pivot Table. I have located the source data specified in the "PivotTable Tools » Options » Data » Change Data Source" area of the ribbon. That value is: '\Documents and Settings\xgraham\My Documents\This Data\Pivots for Review\Markets\[Updated 2009 Data.xls]Raw Data'!$A$25:$H$11263 However, the user referred to th...

Add ons integration
I want to develop an add ons for my RMS. And I want to integrate it to RMS so that I can get a button or Menu in RMS store Manager. Is is possible? If it... Do any one knows the way how to do that? You can place a stand-alone application in the 'AddIns' folder in the Store Ops directory, and it will show up in the Add Ins menu in Manager. You can also modify the taskpad html file that appears when Manager first starts up. - Evan Culver New West Technologies HMAMUN wrote: > I want to develop an add ons for my RMS. And I want to integrate it to RMS so > that I can get a ...

get change operation on coCustomLinks cannot find table
Hello, I have this client that has deployed their server in Singapore but has a branch in Thailand. Since Thailand also needs to use Great Plains, they have installed a GP 8.0 application in a workstation in Thailand and connects to the database deployed in Singapore. But when the staff in thailand logs onto her workstation, she gets the error "get/change first operation on coCustomLinks cannot find table" then all suceeding document processing (i.e. GL entries), go haywire - the next journal entry number doesn't generate. They went through the same installation procedures ...

Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...

calling function in another UI thread
I created another UI thread from the main thread using AfXBeginThread. I defined a new function in the new UI thread. Can I directly call this function in the main thread, or do I have to PostThreadMessage? If I can directly call the function, what kind of IPC mechanism is it? I am little confused. Thank you! class CNewUIThread : public CWinThread { ..... void MyNewFunc() { .... } ......... } a thread does not have functions, it is a sequence of instructions, usually including a loop that processes messages or does some other computation until it either finishes its work or is killed by...

Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...

I need help with a table formula/mass editting
I have a spreadsheet (.csv) with 1600 products/rows and several columns. One of the columns that has the product descriptions also contains 1600 rows. In each row under that 'description' column is a formula/code to input a chart/table with product details to our web site in a chart form. I have many new products that I need to place on the web site with this layout. I need to know how to optimize that particular column to add specific details to the chart formula for the 1600 products. For instance, I need the chart to be the same on each product but with different ...

Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...

Using table valued parameters to delete and update a table
I know how to insert into a table using table parameters, but how do you delete/update with them? The same way you would use a regular table to perform delete/update: MERGE INTO TargetTable AS T USING @TableValuedParameter AS S ON T.keycol = S.keycol WHEN MATCHED THEN UPDATE SET datacol = S.datacol; DELETE TargetTable WHERE EXISTS(SELECT * FROM @TableValuedParameter AS T WHERE T.keycol = TargetTable.keycol); UPDATE TargetTable SET datacol = (SELECT T.datacol FROM @TableValuedParameter AS T WHERE T.keyco...

filling cells acording to the first 3 numbers
How can the following be made (excel 97 SR -2): in collumm A i have numbers (212021740, ...) i want to fill de collumms B and C acording to the first 3 numbers (212). Collumms B and C must be automaticaly fill in agreement with some rules ex: 212XXXXXX Name1 code1 239XXXXXX Name2 code2 241XXXXXX Name3 code3 .... ... ... Thanks :-) +-------------------------------------------------------------------+ |Filename: Relat.zip | |Download: http://www.excelforum.com/attachment.php?postid=3795 | +-------------------...

MS CRM Installation version 1.2 Licence key problem
Hi I have got MSDN version of MS CRM 1.2. There is a Product key on the box of the cd and it said that go to link below to get a licence key by this product key in the readme.html in the CRM setup CD 1. Bu I couldn' get a licence key by this way do you have any idea about this situation or may you know another web address to get a licence key for MS CRM 1.2 Thanks Göksal Ersen Çelebi Senior Software Engineer Hi, the license key isn't on the case of Disk1, you may contact Microsoft and tell them your company name, they will give you a License key. Alternately, you ...