Posts by MrkFrrl

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Show Image Based On Cell Value

    Re: Show Image Based On Cell Value


    Quote from Excelintern

    Okay, fyi my original code worked, I just had a naming error.


    Your code has unnecessary info. It looks like there are two macros there (one for a message box and another to get your picture). Is that what you meant?


    I haven't tried it, but it should've been written like this:



    I don't know if this would be useful, but I've explained how to create a conditional graphics in a workbook without using VBA.

  • Startup Prompt External Links

    Re: Startup Prompt External Links


    Well, providing you've already gone through the menu process as previously noted, I really have no idea why this would occur--unless of course you've got a broken link, in which case you'd see an Error when you go to Edit Links box and would need to update it.


    Quote from Muppley

    It says "this workbook contains one or more links than cannot be updated" withe options to "Continue" or "Edit Links..." I click edit links and the status is showing "OK" and Update is "A", which I guess means Automatic.

  • Startup Prompt External Links

    Re: Annoying Startup Prompt


    You've already adjusted the menu's Edit [COLOR="Red"]->[/COLOR] Links [COLOR="red"]->[/COLOR] Start-up Prompt [COLOR="red"]->[/COLOR] Don't Display the Alert and Update Links [COLOR="red"]->[/COLOR] OK settings, and it still won't prevent this from occurring? What exactly does your message say?


    Quote from Muppley

    :smash: I have linked a data worksheet to a workbook in the same folder, but even though I have selected "Automatic Update" and "don't prompt", I am still getting the Startup Prompt when I open the workbook.


    The status of the linked worksheet is "OK" and there doesn't seem to be any issue regarding the link. I have checked the settings in Tools>Options>Edit and the "Ask to update..." option is unchecked.


    Does anyone have the answer to this annoying "pop-up" for me?

  • Insert Symbol ®

    Re: Insert Symbol ®


    Adding the ® symbol to a cell that already has existing text would be easy.

    Code
    ActiveCell.Value = ActiveCell.Value & "®"


    You'd just assign a key-code shortcut for your macro.


    The problem here, however, is that you "probably" will want to add the registered-symbol immediately after entering the text in a cell.


    Of course, needless to say, while the cell is still active, you won't be allowed to activate a macro.


    The trick then would be to activate this key-code activated macro immediately after hitting the Enter key so that it changes the text in the above cell:


    Code
    ActiveCell.Offset(-1, 0).Range("A1").Value = _
ActiveCell.Offset(-1, 0).Range("A1").Value & "®"


  • Disable Printing Of A Worksheet

    Re: Disable Printing Of A Worksheet


    I tried this in the workbook module to only print Sheet3, and it seemed to work OK.


    You might need to set-up separate print buttons, however, if you want to allow them to both print up a single worksheet and the entire workbook.


    Using this approach, you could make MyTest a public variable, and then have the macro PrintMyStuff (and its "equivalents") in a standard module.

    Code
    Public MyTest as Boolean

  • Vba Autofill Entire Row

    Re: Vba Autofill Entire Row


    Code
    Selection.AutoFill Destination:=ActiveCell.Range("A1:Q1"), Type:= _
        xlFillDefault


    The references above to A1 and Q1 are simply place-holders. A1 would mean whatever cell you begin is the first, and Q1 would mean 16 columns over. So if it begins in column D, it would stop at column T.

  • Printing Embedded Pdfs

    Re: Printing Embedded Pdfs


    I honestly don't know if this can be done. I tried it myself, trying to change some things, without much luck. The best I could do was open an embedded PDF, but that's a far cry from printing it and then also closing it afterwards:

    Code
    Sub OpenPDF()
Application.DisplayAlerts = False
    ActiveSheet.Shapes("Object 1").Select
    Selection.Verb Verb:=xlPrimary
Application.DisplayAlerts = True
End Sub


    Does anyone else want to give this a shot?


    I saw a program out there that converts PDF to Excel: PDF to Excel Converter 2.2 . You might have to go this route first?

  • Open Files From Database

    Re: Open Files From Database


    After reading your post, I wasn't sure what you wanted. Are you simply trying to open a file on your network, or are you trying to query a database?


    If the former case is true, and it's a workbook, you need to use the workbook open command.

    Code
    Workbooks.Open Filename:= "C:\YourPath\YourFilename.xls"


    Or to open something else with Excel (in Excel) as text:

    Code
    Workbooks.OpenText Filename:= "C:\YourPath\YourFilename.txt"


    There are also other ways to open microsoft applications with VBA. If not a microsoft application, you can create a batch file and use VBA's shell command to execute it. Or, sometimes, creating a hyperlink and following it works too.


    If the latter case--that is, you're trying to query a database and retrieve information--your question might be better answered at the Excel SQL forum here.

  • Countif 3 Conditions

    Re: Countif 3 Conditions


    Quote from wildearth2001

    In cell F3 on 'Summary' I want the number of cells in 'Attendance Log'!$F$3:$IV$65536 which contain the letter P...


    I'll give you this much.
    =COUNTIF('Attendance Log'!F3:IV65536,"P")


    The rest sounds too confusing to contemplate.

  • Vba Editor Freezing

    Re: Vba Editor Freezing


    It's really difficult to guess without knowing more. Aside from doing what the other people suggested, do you have a lot of arrays or other memory-intensive formulas (sum products, vlookups, etc.)? That might be consuming a lot of resources. Any On-Time macros? What about Hidden or even Very Hidden worksheets? Sometimes it helps if you paste all your code into Notepad, delete your modules, save your workbook, then create new modules and paste the code back in (don't forget to make a back-up copy first). Could be a corrupt workbook too, in which case you'd need to copy everything to a new one.

  • Macros Cause Closed Files To Open

    Re: Macros Cause Closed Files To Open


    Wouldn't really know where to begin without seeing some of the code in question.


    Is it invoking another workbook's macros by opening it? If so, do this:

    Code
    application.enableevents = false
' code
application.enableevents = true


    Have you tried stepping through the code (pressing F8 repeatedly while in the macro, keeping the VBE at half-screen so that you can see the code and its effect on the worksheet)? This should then show at what point it's occurring.

  • Spreadsheet With No Data Is Too Big

    Re: Spreadsheet With No Data Is Too Big


    Are you viewing all the worksheets? Could some be Very Hidden (which wouldn't otherwise show under the Format menu)? Try this to check for Very Hidden worksheets:


    Code
    Sub AllSheetsVisible()
Dim sht As Worksheet
For Each sht In Worksheets
    sht.Visible = True
Next sht
End Sub


    Do you use your workbook to query a database? Often, data related to the query will be retained, depending on how you set up code for your query (and particularly so if there is no code for the query), and remain hidden somewhere in Excel-land. Sometimes, data can also be shifted-over from queries. You'd then need to select the areas encompassed by the queries and delete it with code. Selecting the delete key doesn't always delete the data, and might only "clear" the contents (though the actual data could be retained in the background). I had a similar problem with a workbook, which continued to magically grow but had no more data; and this resolved the problem for me. I forget exactly how I set it up offhand, but see if something like this doesn't work on a back-up copy:


    Code
    Sub DeleteData()
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Delete
Thisworkbook.save
End Sub


    Then, check the file-size.


    Quote from wisesap

    Then I deleted all columns and rows, cleared all columns and rows in the remaining tab. Now I have one blank spreadsheet in a workbook; no hidden columns, no hidden rows, no hidden sheets. The file is now 8.77mb. How can that be?????
    :(

  • Showing Dropdown Arrow Box In Validation

    Re: Showing Dropdown Arrow Box In Validation


    Quote from farful

    I have many validation drop down lists on a spreadsheet. Right now as the default, if the cell is NOT selected, the dropdown arrow to the far right does not appear. Is it possible to somehow always show these arrows even when the cell is not selected? Kind of like the dropdown lists from User Forms - they show you the dropdown arrows as long as there are choices to be selected.


    Not really. What you could do, however, is format the cell as grey, and change the cell's line shading (bold to the right and bottom, thin at the top and left--to give it a 3D effect), and then put in a couple graphics representing arrows (from the Menu - View - Toolbars - Drawing). Then, above the cells, put in "Click to Select" or something of that nature, so that they know the cells have choices.

  • Multiple Format For Single Cell

    Re: Multiple Format For Single Cell


    I think that would show the decimal places regardless, which I think he wanted to avoid in numbers above 1,000.


    You could try something like #,###.## , but then you'd always show a decimal point regardless.


    Quote from xlite

    i must be missing something here,
    couldnt you just use the custom format: #,##0.00 ?

  • Multiple Format For Single Cell

    Re: Multiple Format For Single Cell


    Format a cell (right-click on the cell, select format) as a Number, determining how many decimal places you want it to display.


    Then, insert this formula, which should work for any number below 1 million to 0.0 (however many decimal places chosen in format).


    It looks at cell A1, which would contain the number you wanted formatted, so you'd need to change those references below for whatever cell needed:


    =IF(AND(A1>=1000,A1<1000000,NOT(ISERROR(FIND(".",A1,1)))=TRUE),(LEFT(LEFT(A1,FIND(".",A1,1)-1),LEN(LEFT(A1,FIND(".",A1,1)-1))-3)&","&RIGHT(LEFT(A1,FIND(".",A1,1)-1),3)),IF(AND(A1>=1000,A1<1000000,NOT(ISERROR(FIND(".",A1,1)))=FALSE),(LEFT(A1,LEN(A1)-3)&","&RIGHT(A1,3)),A1))


    If the numbers are above 1 million, you'll have to figure out how to take it from here.


  • Dynamic Validation List

    Re: Dynamic Validation List


    After creation of your dynamic validation list, have the cell that references it point to the first entry on the list:

    Code
    Sheets(2).Range("A2").Value = Sheets(1).Range("A2").Value


  • Returning A Cell Value

    Re: Returning A Cell Vaule


    I "assume" here (1) that the second page is a worksheet named Sheet2 and (2) that cells C2:G2 are merged:
    =Sheet2!C2
    If the cells you reference are not merged, it would be:
    =Sheet2!C2&Sheet2!D2&Sheet2!E2&Sheet2!F2&Sheet2!G2


    Quote from Ray_n_yellow

    On page 2 in cells C2:G2 I have a name like Joyce and I would like to have the name on page 1 in cell B2 without retyping the name. What formula would I use to do this.

  • Copy 2 Sheets And Close

    Re: Copy 2 Sheets And Close, variable Wbk Names