Posts by vwankerl

    Re: Scrolling Text in a cell

    This code will scroll text in a Shape you place on the ActiveSheet. It is set to use shape named Oval 8. the .OLEFormat.Object.Font.Bold line seems to not work in Excel 2003. If you properly split up the code in proper lines, comment out the .OLEFormat line and place an Oval shape on the worksheet and use the name "Oval 1" then this will work. It does not scroll text in a cell though.

    Re: VBA value error

    Remove the sind = application.sin() and the sqrt = application.sqrt() and the cosd = application.cos() lines. in the formula change sqrt to sqr, change sind to sin, and change cosd to cos. It will compile correctly that way and the sind=application.sin() is not a valid expression in VBA. I can't say if the formula produces the correct results, but you should not have any errors.

    One other thought, in the Function Rfunct(...) line, you should set the type of each parameter like so:

    Function Rfunct(d as Double, m as Double, ...).

    If you don't, VBA will make them variants which may work but is not good technique.

    Re: VBA Code to make multiple sheets visible

    How about setting up an index sheet that would hold a list of "Names" for each set. Then when you wanted a set visible you could double click on the set name to make them visible (or invisible)? The index sheet could also list the sheets in each set. If this sounds like it might work, you could use the Worksheet_BeforeDoubleClick event in the IndexSheet to do most of the work. Let me know if this sounds like something that will work for you. I could work up a mockup of it to show you.

    Re: excel formula to replace certain values in

    I am still confused about parts 1-3. Part 1 says to establish the dates in column B and C. What does "establish" mean?

    In part 2 do you search only column B for either of the dates from part 1? For example, in the above sample it appears you find 1-Feb-2011 and 1-Mar-2011 as the dates in column B and C. Should I then search column B for both 1-Feb-2011 and 1-Mar-2011 and then handle them based on the other rules?

    What should happen if, in Column F and G, there is something other than "YES and 0" or blank?

    Re: excel formula to replace certain values in

    I would like to help with this but your description of the logic you want to implement is not adequate. When you say "lookup the date in column B and C", what is the formula supposed to do with the date? A lookup implies there is something to lookup, what is that something (a user entry, another cell, ??)?

    Maybe some more detail would help.

    Re: Hide Unused Controls in Userform

    Just from a first look at the code, I would suggest the following changes. These are not tested, but are consistent with VBA object enumeration and property management.

    For Each ListBox In UserForm1
              If ListBox.RowSource = Null Then ListBox.Visible = False
          Next ListBox
         For Each Label In UserForm1
              If Label.Caption = Null Then Label.Visible = False
          Next Label

    Re: Need to retrieve multiple excel sheets into one based on due dates

    I have downloaded the workbooks you posted. I have a couple of questions for clarification.

    1. Is there any problem using VBA to do this? I don't think there is any way to solve this without VBA.

    2. You say you want to see the information on those reporters that have due notes on one sheet. Can that one sheet be the "Court Reporters" sheet?

    3. Is it possible for a single reporter to have more than one due at a time?

    4. What information do you want to see from the sheets that are due?

    Re: Macro fire before previous macro has completed

    Since I can't see your code, I am going to suggest some things to consider.

    I have never done a webquery in VBA, but is there a way to detect when the webquery is complete? or can you do the webquery in such a way that it blocks further execution until it is complete? Often with database access there is a way to do a query synchronously and asychronously which means halting code execution until query is done or continuing code execution while query is running. The second method will usually allow you the test for completion of the query so you can process the data knowing it is complete.

    Re: Create Relation between workbooks

    You have two problems here:

    1. MsgBox when workbook opens
    2. forcing both files to move/copy together.

    The first problem is solved with a simple AutoOpen procedure.

    The second is more difficult. You are trying to control how the operating system (Windows) manages files. And as far as I know, Windows does not provide a way to link two files together so they behave as one when moving/copying.

    You could expand the AutoOpen procedure to check the folder the workbook is in for the second file and throw an error message if it is not and then close the workbook. That will force the user to be sure the files are together. As far as I know there is no way to force the two to stay together.

    Re: Enable or disable buttons based on drop down selection

    Try this for the OptionButton code:

    Note that the Range.Locked statements are bounded by the ActiveSheet.Unprotect, ActiveSheet.Protect statements. You cannot change a cells Lock status when the sheet is protected. Just put all the ranges to lock/unlock between the unprotect/protect statements.

    Re: Allowing multiple users in over server

    Basically, Excel does not adequately support multi-user access to the same workbook. There have been attempts to make this work but have not proven to be reliable. The solution is to not use Excel for simultaneous multi-user applications. That sounds pretty drastic, but short of someone else with a proven solution, not using Excel in this way is the best. You will need to redo the application in some other way that properly supports multi-users.

    Re: application.inputbox doesn't control screen coordinates

    I am not sure what the problem is. I copied your code to a test workbook, changed the Left to 100 and the Top to 80 and added DIM Choice as Variant, prompter as String. Ran the code. It displays the Inputbox where it should be, accepts a numeric value that goes into Choice, or if Cancel is clicked then Choice is False. if Escape is pressed, Choice is False also. The Type parameter does not trap the Escape/Cancel key; it just defines the acceptable type of input for the box (e.g. numeric, text, range, etc.)

    If I am missing something here, let me know but I think your code is working (provided the Left and Top values are not too large).

    Re: Show last modified information

    Instead of copying this code to every sheet, why not use Workbook_SheetChange event at the Workbook level instead of the Worksheet_Change event? That will reduce the amount of code to manage and allow for more sheets as needed.