Posts by MrkFrrl

    Re: Print Hyperlinked File, Move To Next


    Are the applications referenced by the hyperlinks from a separate Excel workbook, or are they from another application altogether (e.g., TEXT, HTML, etc.)?


    If we're talking about other Excel workbooks being hyperlinked, this should work (but beware of auto-open macros). The code should begin with the sheet that has the hyperlinks selected.


    Re: Add A Line When Another Cell Is Selected


    This would do it just once (per Excel being opened) when invoked from the Change Cell Worksheet event, which I assume you're using here.


    Code
    static y%
    
    
        'Multiple Start Stop Dates
        If Range("B8") = "X" and y = 0 Then
        Application.Run "InsertRowsAndFillFormulas", 1
        y = 1
        End If

    Re: Booking and Tracking System


    It sounds like you're asking folks here to do all your homework, when you haven't even tried anything yourself.


    I'd recommend using a standard CountIf function to count the number of double-dashes.


    Then, you could use a standard IF function to compare the double-dashes to the age group.


    Then, you should be ready to start your macro--after which you'll probably get more help.

    Re: Trucking Company Pay


    Well, first of all, and correct me if I'm wrong, it sounds like you're mistaken about some things. If it's a semi-paved road, you multiply the value by .9? You're saying he's paid LESS to go down a semi-paved road? Shouldn't it be he's paid 90 percent EXTRA (1.9)? And you say a Dirt Road should be multiplied by 1 (times itself)? I would think you meant he's paid DOUBLE (times 2); after all, it's obviously more difficult. Maybe, I'm mistaken, as this amount may have already been included in the above amount, and so this could be an additional amount you mention.


    In any case, what I'd do is have some cells relating to the various conditions. Say in one area you calculate the normal trip amount. This amount is shown in, say, cell A10. Have another cell next to that (say in cell B10) with a Data Validation drop-down box with either "Dirt" or "No Dirt" showing. Then, have a cell next to that (say in cell C10) for where you'd put in the Dirt Road Distance. Then, next to that, in cell D10, put in an IF formula--something like this:
    =IF(B10="Dirt",2*C10,0)
    Then, do similar things with the other areas, and have them all added together.


    Re: Set Print Area Based On A Condition



    Re: Run Timer Error When Calling Userform


    Baffled why you'd get a subscript out of range error on some computers and not others. User rights?


    Try using a Form button (View - Toolbars - Forms) with a macro in a "public" module instead of a Command button to invoke the UserForm and see if that makes any difference.

    Re: Null Values From Retrieved Spreadsheet Data


    I suppose it could be related to the different versions of Excel, as it appears (?) that you suspect.


    Have you tried stepping through (hitting F8 repeatedly while in the module) the code under your co-worker's log-in on his computer to see what is happening to the values as they transfer? That would be where I'd start.


    If your co-worker doesn't have access to all the directories that you do, it might also cause the problems retrieving values. Have him try opening the workbooks that the sheet references. If that's the problem, you could simply copy the sheets that are referenced to a directory that he can access.


    It's really hard to guess here with what little you've provided. What's the code you're using that causes the values to return?

    Re: Sumif/validation - Range Error


    Here, I named the first range as PubNight and the second range as SampTrain. If you use this formula AND enter it as an ARRAY, it should work. To enter as an array, press CTRL-SHIFT-ENTER after you put the formula in the cell.


    =SUM((PubNight="Pub")*(SampTrain="Training"))


    Re: Hyperlink Within Workbook Then Autozoom


    It should work. Don't see why it wouldn't. You are putting the code in the ThisWorkbook MODULE--correct?


    Quote from Justin Doward

    Hey there,


    Thankyou for the reply, I have tried that code (I found it during a search) but could not get it to work. I have placed it both in the individual worksheet code and in the THISWORKBOOK page but neither appears to work.


    Do I need to write the hyperlinks as macros in order for this method to work?

    Re: Workbook_open


    Code
    Sheets("Sheet5").Select


    Re: Vba - Summing Data Values


    You should know where your VBA puts the data, beginning with whatever cell you deem as the first. If Cell A4 was the first cell, this would sum all the data below it:


    Code
    Sub ShowSumOfArea()
    Dim MyValue As Long
    ' select first cell of data area
    Range("A4").Select
    MyValue = WorksheetFunction.Sum(Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Value)
    MsgBox MyValue
    End Sub


    Quote from nuttytart1987

    How do I edit the VBA so that the 1st data cell is selected, and then the program calculates the sum of the data values, and then stop summing when I reach end of data?

    Re: Option Button On Sheet 1 To Change Image On Sheet 2


    What's it doing now with your code?


    Could you avoid VBA altogether here with a Data Validation ComboBox and a Conditional Graphic? Just a thought.


    Re: VBA - prompt to enter data


    Code in the above message should be amended to include that.


    Quote from nuttytart1987

    Thanks, greatly appreciated, don't suppose you know how once i've entered the data into the message box, I can get it into the first empty cell at the end of the data?

    Re: VBA - prompt to enter data


    Use an InputBox--something like this:



    Quote from nuttytart1987

    After recording a macro that goes to last figure in data, how do I edit this to prompt for a new data value and enter it into 1st empty cell at end of data?

    Re: Speeding Update Process Of A Database


    Go into your Visual Basic Editor, click inside your macro, change the size of the window to half-size (so that you can also see your worksheet), then hit F8 a bunch of times so you can see what's going on inside your sheet while you advance the macro one line at a time.


    If it's nothing, then perhaps you've got a lot of formulas that are taking up your system resources (e.g., Arrays, Vlookups, Info, Indirect, etc.).


    There's no way it should be taking about 5 minutes for whatever you're doing.


    Re: Speeding Update Process Of A Database


    This doesn't make any sense--to me, at least. It looks like you're setting calcMode to application.calculation . Then at the bottom you're saying the (calcMode) application.calculation = application.calculation. The same can be said of the ScreenUpdating. Also, I don't think there's a need to calculate when it is reset to automatic calculation, as it does it right then.


    In any case, I think it should be something like this:



    Re: Delete Macro


    Are you getting some errors? If so, what are they? What happens when you try to step-through the code (hit the key F8 to advance one line at a time in VBA). Are you getting a compile error? If you do a ThisWorkbook.Save before the procedure that bugs out, does it make any difference? Need more info to be sure what's going on.


    Quote from emrena

    after importing data (data >> get external data >> import text file) and running a macro that contains

    Code
    Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp

    . i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before?


    example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. after i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?

    Re: Add Item To Combobox On Sheet


    You could take the easy way out and use Data Validation and get your ComboBox when the cell is selected without the code.


    You'd first have a list of what you want in the sheet. Select the cell in which you want the cell to appear. Then, go to Data -> Validation -> Settings -> List. Then select the cells where your list is at. After clicking OK, whenever you select the cell, you'll be greeted with a dropdown box in the list.


    There is a shortcoming in that another sheet may not reference this. However, if the sheet isn't too terribly complex, you can use the indirect function to override that concern. (I say not "too complex" because I understand the indirect function, as well as arrays and vlookups, may use some system resources if you've got a lot of them.)


    Just a thought.


    Quote from mikezang

    I want to add combobox to my sheet in vba code, then I hope I can add some items to this combobox.


    I knew how to add combobox to sheet, but I couldn't find any information about how to add item to that combobox, does anyone help me?

    Re: Macro On Multiple Worksheet, Different Ranges


    When you say "multiple ranges," you're referring to this as cells that are spaced similarly apart but in different areas?

    Code
    Range("schedule!H4:H35,J4:J35,K4:K35,M4:M35,N4:N35,P4:P35,Q4:Q35,S4:S35,T4:T35,V4:V35,W4:W35,Y4:Y35,Z4:Z35,AB4:AB35")


    Just making sure. And how will you select the first cell in the series?


    One way to do this is use a reference like Cells(x, y) , where x is the row and y is the column. And then you can use something like Cells(x + 1, y + 1) as other references, I believe. For example, Cells(1, 1) would be the equivalent of A1. To say Range("A1:E5") in VBA, you'd use:

    Code
    Range(Cells(1, 1), Cells(5, 5))
    ' [B]or[/B]
    x = 1
    y = 1
    Range(Cells(x, y), Cells(x + 4, y + 4))


    For easy reference, you can quickly change the column headings to a R1C1 reference and back with this code:

    Code
    Sub LetterColumns()
      Application.ReferenceStyle = xlA1
    End Sub
    
    
    Sub NumberColumns()
      Application.ReferenceStyle = xlR1C1
    End Sub


    To apply this on various worksheets, you'd set up the sheetname as a variable. Or, if it's sequential sheets and you're lazy like me, you could leave the sheetname blank and do something like this for each sheet:

    Code
    ActiveSheet.Next.Select
    ' or to select the previous sheet
    ActiveSheet.Previous.Select


    Quote from Avis Guy

    I am looking for a way to modify the following macro so that I can run the same code for multiple ranges that are on different worksheets, any help GREATLY appreciated...




    Re: Creating One Hyperlink At A Time In Vba


    I'm sowewhat confused about what you're asking. Are you generating new names each time in the Summary sheet to be used as a reference for the creation of a new sheet?


    Here's something I just put together to add a sheet and then put in a hyperlink to it from a sheet named Summary. It seems to work OK, but you have to remove Option Explicit from the top of the module's header. I don't know why; I must be overlooking something and don't have the time to figure out why. In any case, maybe it's better in a module by itself for that reason.