Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Regressing A "don't show me this message again"

Hi,

1st post and all - nice to meet you all

This is on Excel2000 !

In the course of resolving a problem involving a report which was taking an age to collate & present data (multiple massive pivot tables & data sources) I've stumbled across a means by which to have the report open & refresh quicker.

This method involves having access to the "Query Refresh" pop-up box which presents itself at the start in order to disable the refresh; unfortunately for me it appears some people have previously selected the option to alway automatically enable refresh and never show the prompt again. I've attached a picture of the prompt in question.

Does anybody know how I'd go about reverting this process so that people are once again presented with the dialogue ? I have a feeling it may involve registry settings but then if I knew for sure I wouldn't be asking

Thanks in advance for any help you can afford me !


Post your answer or comment

comments powered by Disqus
I have a label message on a userform, and I'd like to put an option button on it with the caption "Click here if you don't want to see this message again". I was wondering if there was some code I can use to:make the userform permanently invisible, orterminate the userform permanently, ornot have the userform show up after 3 appearances, orsome creative way to accoplish thisYour help would be much appreciated.

can anyone send me a file showing me how pivot table works pls?

thanks

e-mail: gamestore@hotmail.com

I need help to this : When i type a number to a cell and press enter , i want to check if this number exist in a range of cells (in sheet 2) , and if exists , excel show me a message. Actually i use it for my dvd club. Number is the client code. When i writte 50 in a cell , i need from excel to check if this client own me money , and show me some message..
Thnaks

Hi there! I am using MS Excel 2007 and I am having to prepare a database that takes data from other sheets and places them on sheet one with a specific sequence. I'll explain, my data is in the following form:
Sheet2 A1 to A1617 (each a different value)
Sheet3 A1 to A1617 (each a different value)
Sheet4 A1 to A1617 (each a different value)
....
....
....
....
...
...
Sheet13 A1 to A1617 (each a different value)

The result I require is so that on sheet 1 I have a database showing me this

A1: Sheet2 A1
A2: Sheet3 A1
A3: Sheet4 A1
...
...
...
...
A12: Sheet13 A1

A13: Sheet2 A2
A14: Sheet3 A2
A15: Sheet4 A2
...
...
...

all the way to

A19404: Sheet13 A1617

Is there a way I could get this done automatically? I know the basics of Excel and this has been a dilemma lately. I have several databases to work in this fashion and I do believe that there must be a way to solve this in excel so that it fills automatically. I would appreciate any help with this. Thank you in advance!.

I downloade the VB6cli.exe from this site http://support.microsoft.com/kb/194751

when I click on it in this folder: C:Documents and SettingsnaderLocal SettingsTemp it show me this message "visual baisc 6.0 was not detected on this system"
what is the solution for this problem.

A colleague showed me this strange problem today

He had a vlookup function which instead of returning the answer insisted on
showing itself as text in the cell

so

=vlookup(a1,c1:d4,2,0) would insist on showing as just that

when it was copied and pasted into the cell editor box in another cell it
would work fine

all the other functions were working so the show formulas instead of results
setting was not the problem

Any ideas?
Cheers
--
--------------------------------------------
Please reward mine and others efforts - indicating whether our posts were
helpful by clicking the buttons, ta

Good Morning,
I am having issues with the links function of excel to were it displays the following:
"Excell cannot update one or more links in this workbook. To update the links, open alll teh link source files (Edit menu, Links command). To be sure all calculations are updated, press F9"

The spreadsheet that I created as a template give me this message every time that I try to update. This spreadsheet is obtaining information from another spreadsheet that also contains links as well. Is there anythign that I can do to turn this off and update without opening the source files?

I'm trying to run a regression analysis in excel, but it keeps giving me this message: "Regression - LINEST () function returns error. Please check input ranges again." Basically, I have a whole bunch of historical data I'm running the Regression on. With all the historical data there, the regression runs just fine, but when I deleted some of the old data, it gave me this error message. I looked online for some solutions, and saw that it gives this error when there are blank cells, but I checked over and over again in the input ranges and there are NO blank cells. All of them are either populated with a 1 or 0. I even copied only the data I needed to run the regression analysis on to another spreadsheet, but it still gave me the same error. PLEASE HELP!!!! This is driving me crazy.

Hi

I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind).

This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox.

This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error.

Can someone show me what I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message.

Below is my code:


	VB:
	
 cmbFind_Click() 
    Set c = Range("a65536").End(xlUp).Offset(1, 0) 
     'write userform entries to database
    Dim DataSH As Worksheet 
    Set DataSH = Sheets("ComplaintData") 
     
    With DataSH 
        .Range("L1").Value = cmbSearch.Value 
        .Range("L2").Value = TextBox1.Text 
        .Range("N1").CurrentRegion.Clear 
        .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"),
copytorange:=.Range("N1") 
    End With 
    lstSearch.RowSource = vbNullString 
    lstSearch.RowSource = "outdata" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
would appreciate any help! Cheers

Hello, I have been racking my brain to try to make this work, but so far I am unsuccessful. What I am trying to do is count how many times a persons name shows up on a certian date. Please see Example 1.

The forumlas I have tried so far have not worked, and have only ended up counting every time someone's name was entered into the sheet or not at all. So I am wondering if more knowledgable folks can assist me with this.

Example 1
example1.jpg

Bob's name shows up twice on March 9th. Over on the calander, I am trying to get the total number of times a person showed up on a day, transfer into the calander in example 2. Basicly, check if column B has X day, count the name in column D.

Example 2
example2.jpg

This is all on one worksheet to try to keep it simple

It's probably something simple that I am just not seeing, but any help with this is greatly appreciated.

Thanks
Jon

Hi all,

I have a Book1.xls which has 4 worksheets: Select, Sheet1, Sheet2, Sheet3.
Inside "Select" worksheet, I created a combo box which its values are inside A2, A3 and A4 cells and are as follows: Sheet1, Sheet2, Sheet3. (The combo box and its values are all within the same sheet, "Select")

I then selected the combo box and right clicked on it and then selected Assign Macro option and then clicked New. Then I wrote some VBA code so that when someone selects the Sheet1 from the Drop down, the sheet1 opens automatically. Same scenario if the used selected Sheet2, Sheet3...

This is the code, but everytime I select anything, case 3 runs and it shows me the message box. Please help me figuring out the problem here:

Sub DropDown11_Change()
Select Case DropDown11
Case "Sheet1"
Workbooks("Book1").Worksheets("Sheet1").Activate
Case "Sheet2"
Workbooks("Book1").Worksheets("Sheet2").Activate
Case "Sheet3"
Workbooks("Book1").Worksheets("Sheet3").Activate
Case Else
MsgBox "You haven't selected anything!"
End Select
End Sub

Thanks

Hi all, I have a GPS csv file that gives me columns for speed and time and distance I would like to produce a chart showing the speed as a line plotted against distance travelled
in othe rwords the line would show that at say mile (distance) speed is
I am tottally lost on this one any help much appreciated

I am using the below code to update an INTRO sheet that has current world times of a few places we to business.

I have two buttons, "Clock Update" and "Stop Clock".

Problem-when I protect the sheet and then press the Update Clock button I get a run time error of 1004. I gather this is telling me it can't run a macro using this button on a protected sheet?

Is there anyway around this??

I don't let the clock run all the time because it causes the screen to jump every time the clock updates ...

Dim NextTick
Sub ClockStart()
Run ("ClockUpdate")
End Sub
Sub ClockStop()
' Cancels the OnTime event (stops the clock)
Application.OnTime NextTick, "ClockUpdate", , False
End Sub
Sub ClockUpdate()
Application.ScreenUpdating = False
' Updates the clock that's visible
' DIGITAL CLOCK
ThisWorkbook.Sheets("fil-INTRO Sheet-4").Range("ESTDigitalClock").Value = CDbl(Time)
' Set up the next event one second from now
NextTick = Now + TimeValue("00:01:00")
Application.OnTime NextTick, "ClockUpdate"
Application.ScreenUpdating = True
End Sub


[ This Message was edited by: em on 2002-10-28 10:56 ]

[ This Message was edited by: em on 2003-01-31 23:49 ]

i am attempting to call data from a source (input workbook) into a workbook
(called the output workbook) and when i start up the output workbook after i
am prompted if i want to update or not, i get a message that says the output
workbook contains links to databases (the input file) that cannot be updated.
However, the output workbook gets updated just fine. Is there a way to search
for these links inthe output? (they dont show up when i run a Find-- ".xls"
or "[") If not, is there a way to get this message to dissapear?

If this is a duplicate message, I apologize. I don't think that my first try
succeeded.

When using my Microsoft Excel spreadsheet in Widows XP (that I imported from
Microsoft Works in Windows 98SE) I can't insert a new row (I can insert a new
column). When I select the row and click on "Insert," then "Row," I get the
message: "To prevent possible loss of data Microsoft Excel cannot shift
nonblank cells off the worksheet. Try to locate the last nonblank cell by
pressing CNTRL+END and delete or clear all in cells between the last cell and
the end of your data. Then select cell A1 and save your workbook to reset the
last cell used. Or you can move the data to a new location and try again."

To me this message is meaningless. Any suggestions?

I know perfectly well that I can't plot negative values on a log chart. That
doesn't mean that my data doesn't have negative numbers in it. For years
I've been trying to outsmart Excel so that it effectively ignores negative
data on log plots, but it seems to me that it should simply happen (like it
does in most other mathematical tools). If the user isn't smart enough to
know that he can't plot negative numbers on a log scale, well, warn him by
default, if you must, but allow the skilled user to disable the message! It
is incredibly annoying, and it reduces my productivity by 100% when I'm
analyzing very typical data in thermal tests.

I can turn off just about anything other automatic stuff else that bothers
me (like converting cells with "@" into email addresses, for crying out loud
- that took me a long time to figure out how to disable). What's wrong with
a "don't show me this again" checkbox on the "Negative values cannot be
plotted correctly on a log chart." warning dialog? What's wrong with simply
ignoring the negative data and not plotting it, just like you do with #NA and
#VALUE and all those other unplottable values?

I don't want to have to build my own log charts that ignore negative numbers
(I could, but what's the point of a built-in log chart if you can't use it).
I don't want to have to write formulas for cells to make the offending
negative value "invisible" to the chart (I've tried dozens of ways, and they
all fail to be user-friendly at some level, like making OTHER dependent
formulas fail gracelessly). What I want is to tell Excel that I'm smart
enough to know that when the chart has a hole in it, it's because it couldn't
plot the data. What's so hard about that?

This has frustrated me for years. Can you tell?
--
Roger Stout, PE
Senior Research Scientist
ON Semiconductor

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

Hi - two parts to my question:

1. Conditional formatting has been added to cells in a spreadsheet (if a
value is 0-1, format one way; if = 2 format another way; if greater than or
equal to the number 3 format a third way) - works great. When the value of a
cell is greater than or equal to the number 3, my user would also like a
prompt indicating that the cell contains a number greater than or equal to
the number 3. Is there a way to do this "out of the box"? My user and I are
not programmers, so wouldn't be able to trouble shoot code, if necessary. The
cells that contain the conditional formatting are never touched. There is a
formula attached to those cells that sums a row of numbers.
2. The other thing he would like, or in place of the first request, is to
have a new email message created automatically, based on whether the cell
contains a value greater than or equal to the number 3. He has a column with
email addresses in it, and would like to know if it's possible to have Excel
automatically create a new email message using the email address from the
same row as the cell that contains the >=3 conditional format. I haven't
found anywhere to do this out of the box, so suspect programming would be
needed, but thought I'd ask.

Please let me know if you need any more details. Thanks for any suggestions!

The link will setup your newsreader with the Microsoft Newsgroups for the
Excel
group.

I have no idea how to setup a slide show in Excel, but the folks in the Excel
group can help.
news://msnews.microsoft.com/microsoft.public.excel.misc

Or try he
http://www.microsoft.com/communities...&lang=en&cr=US

--
Mary Sauer MSFT MVP
http://office.microsoft.com/
http://msauer.mvps.org/
news://msnews.microsoft.com

"Suleman" > wrote in message
...
> marry what is that news link how i used it
> please help me
>
>
> news://msnews.microsoft.com/microsoft.public.excel.misc
>
>
> --
> Mary Sauer MSFT MVP
> http://office.microsoft.com/
> http://msauer.mvps.org/
> news://msnews.microsoft.com
>
> "Suleman" > wrote in message
> ...
>> Helo everyone
>> I Want To Make a Slide Show In Excel Like Powerpoint
>

Hello everyone
I am a newbie to excel programming and have my first form done. It is a simple data entry form that lets me add names and info to a spreadsheet. I would like to add a text box and a button that will perform a search for all matching cells in the spreadsheet and put the information in a popup window.

for example button will say search: in the window if i put in a month: December

then all my clients in the database with the cell for date filled in with december will show up in a popup window. I would like to see all the fields of each client with the word december in their row show up. I would like the popup window to have a scroll bar in case i have like 200 hits.

I have been searching on the web for tutorials to show me this but have yet to find one. Please help either with a tutorial or with the function that I should use to perform this action that I would like.

Hi All

For the life of me i cant work out why excel will not show me the answer to the formula when i drag it down the collum it will only show the answer to the first row? is this as simple a clicking something? Please help its driving me nuts. I want excel 2003 back!

Hello,
I'm trying to use a text box to display my data validation input message, but I cant seem to even get the text box to even pop up when I hover over the cell
I have created a data validation list but it doesnt display the text correctly so i thought I would use a text box

I'm using excel 2007, & I selected insert, textbox from the ribbon and named the textbox "txtInputMsg"

can the code be added to the workbook section in the VBA editor or the active worksheet?

I'm also using a named list for my data validation drop down list which resides on a different sheet, does this matter

I'm new to this, I do appreciate the help

Here is the code I'm trying to use:

Option Explicit

Private Sub Data_Validation()

Dim strTitle As String
Dim strMsg As String
Dim sTemp As Object
Dim lDVType As Long
Dim ws As Worksheet
Application.EnableEvents = False
Set ws = ActiveSheet
Set sTemp = ws.OLEObjects("txtInputMsg").Object

'On Error Resume Next
lDVType = 0
lDVType = Target.Validation.Type
'On Error GoTo errHandler
If lDVType = 0 Then
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
Else
If Target.Validation.InputTitle <> "" Or _
Target.Validation.InputMessage <> "" Then
strTitle = Target.Validation.InputTitle & Chr(10)
strMsg = Target.Validation.InputMessage
With sTemp
.Text = strTitle & strMsg
.FontBold = False
'.Characters(1, Len(strTitle)).Font.Bold = True
End With
sTemp.Visible = msoTrue
Else
sTemp.Text = ""
ws.OLEObjects("txtInputMsg").Visible = msoFalse
End If
End If
errHandler:
Application.EnableEvents = True

End Sub


Hi all,

My doubt is probably simple for most of you, but I'm pretty new at this so, better to ask you than keep looking through google.

I've edited a code in word to create a pie chart and format it according the name of the colors using the select case. When this code is run in a excel 2007 it runs pretty good, but once I write the same code in a excel 2003, it gives me a runtime error which say: "Object variable or With block variable not set". below in the code I show where this message appears.

My problem is right in this section, how do I solve this issue in excel 2003, once in excel 2007 is working properly.

Thanks in advance

The code is:
Sub Pie_Chart()
'Choosing what type of chart
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DPieExploded
    ActiveChart.ApplyLayout (6)
    ActiveChart.ChartArea.Select
'Creating the Chart
  With ActiveChart.SeriesCollection.NewSeries
        .Name = ActiveSheet.Range("C2")
        .Values = ActiveSheet.Range("C8:C10")
        .XValues = ActiveSheet.Range("B8:B10")
  End With
'Placing the Chart
  With ActiveChart.Parent
    .Left = 1
    .Width = 215
    .Top = 267
    .Height = 150
  End With
'Formating the Chart
   Dim iPoint As Long, nPoint As Long
  With ActiveChart.SeriesCollection(1).Select 'THE RUN TIME ERROR ON EXCEL 2003 COMES RIGHT HERE
     For iPoint = 1 To .Points.Count
          Select Case WorksheetFunction.Index(.XValues, iPoint)
                 Case "Yellow"
                    .Points(iPoint).Interior.ColorIndex = 6 ' Yellow
                 Case "Red"
                    .Points(iPoint).Interior.ColorIndex = 3 ' Red
                 Case "Green"
                    .Points(iPoint).Interior.ColorIndex = 4 ' Green
           End Select
       Next
   End With
End Sub


I'm new to VB and am having trouble articulating this problem please be patient with me.

I have a VBform that opens when the wkbk opens.
(on the 'ThisWorkBook' script page)

When the VBform opens, it is in front of wkbk.

I want the the wkbk/window to minimize, leaving just the form on the screen.
(not ever seeing the wkbk)

When I put the minimize cmd on the line before the 'show form' cmd, the wkbk/window WITH the form minimizes and then flashes at the bottom of the screen in the taskbar.

I have to click the flashing minimized excel to get the form to appear. The wkbk stays minimized and the form pops up.

NOT what I wanted, on opening the wkbk I want the wkbk/window to minimize LEAVING just the VBform on the screen.

If I put the minimize cmd AFTER the 'show form' cmd, then the wkbk does not minimize UNTIL AFTER I close the form (since the minimize cmd comes AFTER the 'show' cmd...).

I'm sure there must be a way to do this (I think).

ALL help is APPRECIATED, thanks, Phil

I have several cells that have a formula dependent on other cells. Even though the "other" cells are currently blank the cells with a formula show 0. What can I add to the end of my formulas to keep those cells blank until a value is determined?

My worksheet is attached. Examples of what I am talking about are:
B14 C14 B15 C15 I10:I14 J10-14 T10-14

If I could just see one cell with the formula I can apply that to the rest, correct?

Thank you for your help and yes I know this must be simple for most - but not for me.


No luck finding an answer? You could always try Google.