Automatic Comments Boxes

I have a spreadsheet with two tabs that I use for work, One tab is inbound
quantities and the other tab is the order no of that quantity.

What I would like to do is where the quantity in a cell in inbound ie D8 is
greater than 0 then a comment is automatically inserted quoting the value in
D8 in the other tab.

If this is possible it would be a great help.

TIA


0
6/24/2004 3:39:44 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
485 Views

Similar Articles

[PageSpeed] 33

Assuming inbound quantities are in Sheet1,

Put in D8 in the other tab / sheet (for order no.)

: =IF(AND(ISNUMBER(Sheet1!D8),Sheet1!D8>0),Sheet1!D8,"")

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Rob Fenn" <robfenn.mp@nospamntlworld.com> wrote in message
news:ugoe2GgWEHA.2852@TK2MSFTNGP12.phx.gbl...
> I have a spreadsheet with two tabs that I use for work, One tab is inbound
> quantities and the other tab is the order no of that quantity.
>
> What I would like to do is where the quantity in a cell in inbound ie D8
is
> greater than 0 then a comment is automatically inserted quoting the value
in
> D8 in the other tab.
>
> If this is possible it would be a great help.
>
> TIA
>
>


0
demechanik (4694)
6/24/2004 4:14:14 PM
Sorry I think I was unclear

If I have a value in D8 in my inbound tab I want to attach a yellow comment
box to this cell and insert in the comment box the value in D8 on the order
no sheet.

TIA

"Max" <demechanik@yahoo.com> wrote in message
news:eGnYAbgWEHA.3200@TK2MSFTNGP09.phx.gbl...
> Assuming inbound quantities are in Sheet1,
>
> Put in D8 in the other tab / sheet (for order no.)
>
> : =IF(AND(ISNUMBER(Sheet1!D8),Sheet1!D8>0),Sheet1!D8,"")
>
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
> "Rob Fenn" <robfenn.mp@nospamntlworld.com> wrote in message
> news:ugoe2GgWEHA.2852@TK2MSFTNGP12.phx.gbl...
> > I have a spreadsheet with two tabs that I use for work, One tab is
inbound
> > quantities and the other tab is the order no of that quantity.
> >
> > What I would like to do is where the quantity in a cell in inbound ie D8
> is
> > greater than 0 then a comment is automatically inserted quoting the
value
> in
> > D8 in the other tab.
> >
> > If this is possible it would be a great help.
> >
> > TIA
> >
> >
>
>


0
6/25/2004 3:55:52 PM
Perhaps try the sub below (which goes into workbook module) ..

Right-click on the Excel icon just to the left of "File" > Choose "View
Code"
This will bring you direct into the workbook module

Copy and paste the sub below (everything within the dotted lines)
into the whitespace on the right
(clear the defaults appearing in the whitespace first)

----------begin vba------
Private Sub Workbook_SheetChange(ByVal Sh As Object,  _
ByVal Target As Excel.Range)

Set Alert = Sheets("Sheet1").Range("D8")
    If Application.WorksheetFunction.IsNumber(Alert) = False _
    Then Exit Sub
    If Alert > 0 Then
    Sheets("Sheet2").Select
        With Range("D8")
            .ClearComments
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:="Quantity: " & Alert
        End With
    End If
End Sub
----------end vba----------

Press Alt+Q to get back to Excel

Test by entering a figure > 0 in D8 of Sheet1
(Sheet1 is the assumed tab for "inbound quantities")

A comment box will be auto-inserted in D8 of Sheet2
(Sheet2 is the assumed tab for "order no.")

with the phrase:
"Quantity: <Figure entered in D8 of Sheet1>"

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Rob Fenn" <robfenn.mp@nospamntlworld.com> wrote in message
news:OvZyj0sWEHA.3420@TK2MSFTNGP12.phx.gbl...
> Sorry I think I was unclear
>
> If I have a value in D8 in my inbound tab I want to attach a yellow
comment
> box to this cell and insert in the comment box the value in D8 on the
order
> no sheet.
>
> TIA
>
> "Max" <demechanik@yahoo.com> wrote in message
> news:eGnYAbgWEHA.3200@TK2MSFTNGP09.phx.gbl...
> > Assuming inbound quantities are in Sheet1,
> >
> > Put in D8 in the other tab / sheet (for order no.)
> >
> > : =IF(AND(ISNUMBER(Sheet1!D8),Sheet1!D8>0),Sheet1!D8,"")
> >
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > Please respond in thread
> > xdemechanik <at>yahoo<dot>com
> > ----
> > "Rob Fenn" <robfenn.mp@nospamntlworld.com> wrote in message
> > news:ugoe2GgWEHA.2852@TK2MSFTNGP12.phx.gbl...
> > > I have a spreadsheet with two tabs that I use for work, One tab is
> inbound
> > > quantities and the other tab is the order no of that quantity.
> > >
> > > What I would like to do is where the quantity in a cell in inbound ie
D8
> > is
> > > greater than 0 then a comment is automatically inserted quoting the
> value
> > in
> > > D8 in the other tab.
> > >
> > > If this is possible it would be a great help.
> > >
> > > TIA


0
demechanik (4694)
6/26/2004 3:05:41 AM
Just a little clarification ..

The description "workbook module"
should rightly read as "ThisWorkbook module"
(.. less ambiguous)

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
6/26/2004 10:52:53 PM
This works great however I need it too work across a huge range of date
E6:AE729 pulling the info each time from the respective cell in the other
sheet.

Also I need the comment to be removed if the cell is made blank later.

TIA
"Max" <demechanik@yahoo.com> wrote in message
news:uf%23vGD9WEHA.2520@TK2MSFTNGP12.phx.gbl...
> Just a little clarification ..
>
> The description "workbook module"
> should rightly read as "ThisWorkbook module"
> (.. less ambiguous)
>
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
>
>


0
6/29/2004 4:06:18 PM
This works great however I need it too work across a huge range of date
E6:AE729 pulling the info each time from the respective cell in the other
sheet.

Also I need the comment to be removed if the cell is made blank later.

 TIA

"Max" <demechanik@yahoo.com> wrote in message
news:uf%23vGD9WEHA.2520@TK2MSFTNGP12.phx.gbl...
> Just a little clarification ..
>
> The description "workbook module"
> should rightly read as "ThisWorkbook module"
> (.. less ambiguous)
>
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
>
>


0
6/29/2004 4:22:42 PM
Pleasure' Rob. Got you the first time <g>

But think I've just about reached my (in)competence level
(still learning vba ..)

Hang around this thread a while ..

Perhaps others more experienced in vba
(and there are many) would step-in to help extend/re-work
the sub to meet your revised specs below

Otherwise, you could try a post in .excel.programming

Good luck !
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Rob Fenn" <robfenn.mp@nospamntlworld.com> wrote in message
news:Os2sCNfXEHA.716@TK2MSFTNGP11.phx.gbl...
> This works great however I need it too work across a huge range of date
> E6:AE729 pulling the info each time from the respective cell in the other
> sheet.
>
> Also I need the comment to be removed if the cell is made blank later.
>
> TIA


0
demechanik (4694)
6/29/2004 4:57:21 PM
Reply:

Similar Artilces:

automatically sending messages at set times??
Wondering if there's a way with Outlook to send a default message at a specified time?? ie: a message for my ex to repay me her debts on a weekly basis :P "Adam Membrey" <membreya@hotmail.com> wrote in message news:O$2Z4y0zEHA.3416@TK2MSFTNGP09.phx.gbl... > Wondering if there's a way with Outlook to send a default message at a > specified time?? > > ie: a message for my ex to repay me her debts on a weekly basis :P Sure if you are willing to write code but chances are it would be easier to do from the command line with one of the email utilities like ...

KB978542 will not load in automatic or manual mode
initially got error message. After setting new batch files and other files as suggested on this site - now I get message that "files not found" when trying to download manually. Using Windows XP and have loaded all available Pack 1, 2 and 3 updates - through automatic or manually. Latest version of AVG in place and have had no problems with any other updates loading. Have you ever had occasion to do a Repair Install of Windows for any reason? What anti-virus application was installed before you installed AVG (any version) and was your subscription still current or...

VBA Combo Box
I have a combo box that I want to populate with a list of names. I do not want to reference names in the worksheet - using "RowSource" - I want to write the names in the code. I tried the following code: Private Sub ComboBox1_Change() With ComboBox1 ..RowSource = "" ..AddItem "Male" ..AddItem = "Female" End With End Sub This gives me an error. Does anyone know where the code should be added. Is this on a userform? If yes, then maybe it should be in the Userform_initialize procedure? If it's on a worksheet, maybe in the auto_open or workbook...

Outlookm shutting down automatically
I am not sure if there is a conflict but I am running PLAXO which is web based system that updates your contact list. Whatever the case is my outlook keeps shutting down by itself. Iw ill go to read a message and it will auto shutdown??? Not sure what is causing this. I go ahead and send an error report and the problem persists. Please advise as how to fix this problem. Thanks, Chad ...

CLick on Box to enter time of day
I have a worksheet where payment is entered in column C. I would like to use Column B as a time stamp. In other words as soon as data is entered in C5 then the exact time comes up in B5. When data is entered in C6 then the exact time cones up in C5. I do not want this time to change even when the page is saved and opened later on. Elfego You can enter a static time in a cell by hitting CTRL + SHIFT + ;(semi-colon) You could also use event code to enter a static date when you enter something in a cell. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in ...

Automatic Find and Populate
Ok, I have several spreadsheets that represent bills and have tabs that represent the months. So, when I recieve a new bill for the month, I copy last month's bill onto a new tab, and rename it using the billing cycle. Each year, for each account number is contained in its own workbook- ie: account 245-9999 has a workbook for 2002, 2003, 2004, and 2005; within that workbook are tabs for Jan, Feb, Mar, etc. Ok, on a single month's tab, there are anywhere from 10 to 900 individual line items representing circuits. What I want to do is create a separate spreadsheet that lists unique c...

putting an input box inside a macro
Join Date: May 2006 Posts: 19 Input box inside a macro -------------------------------------------------------------------------------- Ok, I need to create a button with a macro attached which allows me to delete columns within a worksheet, in this macro I must include an input box which asks the user to either identify as 'the treasurer' and delete all 'financial' data Sheets("Membership").Select Range("G3:H17,K3:K17").Select Range("K3").Activate Selection.ClearContents End Sub or identify as 'the registrar' and delete all '...

List Box no displaying added choices
I have several drop-down list boxes in a few forms that I am having trouble with. When I originally set up the database, I created the tables and entered a few items to be included in the drop down list in the lookup section of the table (design view). I then created the forms and inserted those fields. I have since added several new choices to the drop-down list of the table, and those new selections are available in the Table, but they are not available in the Form. The only items available in the drop down list on the Form are those choices that were originally there when I set up the fo...

Duplicate message box
Hi... Can u pls.anybody suggest for my below query..? My query is.... I set Primary key for 4 field together as i need to check duplicate values for these 4 field. When i enter the data into the form, it restricts to enter the duplicate values by giving the below message. "The changes you requested to the table were not successful because they would create duplicate values in the index,primary key, or relationship. Change the data in the field or fields thagt contain duplicate data,remove the index,or redefine the index to permit duplicate entries and try again." But, i ne...

reports and list box from table query
i created a report based on 3 tables. One of the tables is tied into a list box/query to get the correct options. However, when i create the report... the list box is also created on the report. With the correct selection highlighted in black. This also shows the other possible options with a scroll bar. I just want the selection to show up on the report.... would it make a diffrence if it was a value list? the options hardly ever change, but there are around 60 options. bl -- brandon Message posted via http://www.accessmonster.com You should not use a listbox data-type in a tab...

Stop Automatic Reduction of Fractions
Excel 2000/2003- In the company, WIP to Lead-time must be stored in a worksheet as a fraction- 4/56 for example. When you enter 0 [space] 4/56 and press enter, Excel reduces the fraction to 1/14. The 4/56 must remain displayed as 4/56 for this task, and remain a number (so preceeding with a space or an apostrophe will not work). Anyone know how to stop the automatic fraction reduction? I don't immediately see any setting. Thank you. Annie Whilst there isn't any way per se to do what you are asking, take a look at http://www.excelforum.com/showthread.php?t=374128. You could ex...

Print dialog box using VBA in excel
Hi Everyone, I like to add a print button (that will select several worksheets and print them) in my excel worksheet. I have recorded my action using the macro recorder the problem is I can't select the printer everytime I run the macro it print in the background without asking the user to select the printer and uses the default printer. Can anyone help?? I just want the user to be able to select the desired printer every time before printing. My current code: Sub Print_All() Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 5)).Select 'intent...

combo box in inputbox
Can combo boxes be used on inputbox in vb? No. If you want to use a combobox or listbox you will need to build a form and call it. Open the form in DIALOG mode so you can stop code processing. Do not close the form when you are finished, but set its visible property to FALSE which will the resume running the calling code. You can grab the value from the form and then close the form in the calling code. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County seeker wrote: > Can combo boxes be used on inputbox in vb?...

Excel L-shaped Text Box
Is there a way to create an L-shaped Text Box in Excel? For example. my text box would contain text in the following layout. AAA AAA AAA AAAAAAA AAAAAAA AAAAAAA If L-shaped text box isn't possible. Is there a work around? Thank you so much for your help! Use the Line object in the Drawing toolbar to make the L shape Remember that holding SHIFT helps gets line parallel to cell grid Make sure the ends all align Click one line; hold CTRL and click each line in turn; use left mouse button to open popup menu and use Group To move L shape let mouse pointer hover over a handle (open circle...

Automatic calculation
Hi I have a workbook that takes an eternity to calculate and therefore, have the calculation mode set to manual. I would like the calculation mode set to Automatic when the workbook is deactivated or closed, but do not want the workbook to be calculated when this happens. Is that at all possible? Thanks in advance Ben You could use workbook events that fire when the workbook is activated/deactivated. Rightclick on the excel icon (to the left of File|Edit|...) on the menubar. Select view code and paste this in the code window. Option Explicit Private Sub Workbook_Activate() Applica...

Removing gray shading in protected forms (drop down box)
Hello- I am creating some contract templates for my company's sales force using Word 2003. I am using the forms toolbar to insert checkboxes and drop down boxes from which to select deliverable specifications. The problem is that when a user selects an option from the drop down box, the gray shading remains. I know the sales teams HATE the gray shading because they don't think it looks very good when sending to customers. Is there a way to disable the shading when an option is selected? Thanks. You can remove form field shading altogether using the corresponding b...

Switch maximize button in a title bar of a dialog box off
Hello, is there a possiblity to switch the maximize/ normalize button in a title bar of a dialog box off (and on) by using a method during program operation instead of not setting the SW_MAXIMIZEBOX option in Create ? What is the Windows-Message if a user presses the maximize button ? Is it possible to overwrite the message handler ? Which method is it ? Thanks in advance for your help. Greeting Alex > is there a possiblity to switch the maximize/ normalize button in a > title bar of a dialog box off (and on) by using a method during > program operation instead of not se...

Boxes instead of pictures.
In some messages, I get boxes with another box and a red X in it, instead of pictures that were sent with the messages. What can be done about this? If you are sure the picture indeed got send; Are you using any anti-spam tool? -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox! www.sparnaaij.net (I changed my reply add...

Comments on Manual Calculation on certain sheets.
I have a sheet in a workbook with array functions that slows down performance with calculation on automatic (which I need for the other sheets). Would it not have been useful to be able to set a single sheet to Manual and update when needed in the same way Pivot Tables work? One way to work around this is to save the Array Formula sheet in another workbook. I have browsed some of the posts on Excel's way of handling Automatic/Manual recalculation and it seems as if there is room for improvement or are there important reasons that I am not aware of? Any comments. Laurence Lombard Hi ...

How do I turn off automatic hypenation for an entire publication?
I don't want publisher to automatically hyphenate every article in a publication. Is there a way to turn off this feature with out turning it off for every text box created? Sure there is, go to "Tools" and "Options", and under the "Edit" tab uncheck the "Hyphenation" box. Easy-peasy! Jim Dixon "jnb1258" <jnb1258@discussions.microsoft.com> wrote in message news:C3D894C1-6E4A-4215-9865-008186A27321@microsoft.com... >I don't want publisher to automatically hyphenate every article in a > publication. Is there a way to tu...

How do I set up an automatic invoice number in Excel 2007
I have just upgraded from Office 2000 to Office 2007. In Excel 2000 I was able to insert a unique invoice number automatically by clicking "add number" on the invoice toolbar. Can a similar action be taken in Excel 2007 and, if so, how do I set it up? Thnaks for any help, Ian Perhaps you need to include an equivalent add-in to what you added to Office 2000? There isn't an "invoice toolbar" in native Excel. It sounds as if using the same add-in isn't trivial: http://www.officekb.com/Uwe/Forum.aspx/ms-excel/93002/Excel-Invoice-Template -- David Bi...

Unable to Migrate Exchange 5.5 mail boxes to 2003 with the Migration Wizard
Hi, We are trying to migrate from and NT4 Domain with Exchange 5.5 to 2003/AD with Exchage 2003. We have the AD Domain set up. The NT4 Domain is still in place and on the same network, so our AD domain is similar but different (the "inc" has been dropped from the NetBIOS name). We have 2 Domain Controllers, and a brand new system to run Exchange 2003 on. It is clean, only has the OS and Exchange on it (we have problems when we get the critical updates. It seems linked to the exchange database going belly up, and we can't install exchange in the first place if the updates are ap...

Can exch2003 distribution list be automatically expanded?
Hi there, For exch2003 & outlook2003, after user compose an email with selecting a dist list from address book, then click send button, how can I configure the system to automatically expand the dist list to a list of users? Well, this could be a strange question, but I really want to do that. Many thanks! This is an Outlook setting. I'd recommend against doing this. If you insist on it, when you add the DL to the To: line, you should notice a little + sign next to the DL. If you click on the + sign, Outlook will warn you that it will replace the DL with a list of members. ...

Combo Box problem 08-04-07
Why is my combo box in both the table and the bound form not accepting the hand-typed value and produces a "value not an item in the list" error even though I know for sure the value IS in the list? When I select the value from the list it wqorks fine and goes on to the next field.... Thanks Hi, Dennis. > Why is my combo box in both the table and the bound form not accepting the > hand-typed value and produces a "value not an item in the list" error even > though I know for sure the value IS in the list? No, the value _isn't_ in the list, because you ...

Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1. How is the best way of doing this. Is there a shortcut....keyboard stroke to do this?? Hit [F4] several times and watch the results. Micky "hrb_2001" wrote: > I want to turn this =A1 into =$A$1. > How is the best way of doing this. Is there a shortcut....keyboard stroke to > do this?? > Hi, Enter =A1 then tap F4 Repeated taps of F4 scroll through the referencing options -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently an...