Posts by Bert01

    Re: Formula To Retrieve Cell Text Not Value/formula

    I was able to duplicate the problem. If the formula is on sheet1 try referencing this modified formula (the actual cell reference doesn't matter):
    =UPPER(MID(CELL("filename",Sheet1!A30),( FIND("]",CELL("filename",Sheet1!A30))+1),50))

    Re: Function/Formulas Not Automatically Updating

    Make sure none of the cells are formatted as TEXT because calculations have a way of not performing properly with this format. If you changed it from TEXT to General or Number the cell may still have to be edited for the new format to "take". If numbers copy a 1 and paste special -> Multiply to reset all the cells.

    Re: Dynamic Chart Not Updating

    First don't miss the obvious and make sure Calculation is set to Automatic under Tools -> Options.

    Next, make sure none of the cells are formatted as TEXT because Excel tends to not read numbers if they have this format. Even after you take the TEXT formatting off you need to either edit the cell or copy a 1 and do Edit -> Paste Special -> Multiply -> OK to get Excel to properly recognize the numbers.

    Re: Saving .xls Files To .txt Format Keeping Column Data Aligned

    Try this:
    1) Highlight the sheet and change the font to Courier New.
    2) Make the first row just normal height.
    3) Highlight the sheet, go into Cell formatting and take off the Text Wrap.
    4) Highlight the columns with text and make each column the width it needs to be to show all of the text, especially headers.

    Save like you were saving before as .prn. It worked for me.

    Re: Open & Hide The File When A File Is Opening

    Just for clarification, by "shared workbook" do you mean you have chosen Tools -> Share Workbook or are you just putting a regular excel file on the network to be shared? Big difference. Also, I still would like to know if you just read from the ref.xls or if you also write to it?

    Like Ken said, you should be able to just read the data from ref.xls into an array or hidden sheet and then close ref.xls.

    Re: Open & Hide The File When A File Is Opening

    How is del1.xls referencing ref.xls? If each delx,xls file is is trying to open the ref.xls file, then that is the problem. It should just reference the file, not open it since it is already open.

    I have developed a shared system but have not attempted what you are doing. I don't see why ref.xls should be opening multiple times. If you are only reading ref.xls and not writing to it then the file should not be shared.

    I hope you have read all the warnings on corrupt data when using shared files. If you need to write to the ref.xls file another (non-shared) way to do it would be to have def1.xls open the file, write, then close the file. If another def2.xls user tries to open the file at the same time it is an error and you can loop and wait 1-10 seconds until def1.xls has closed the file.

    Re: Giving a Range Letter a Name Value

    If you have already created a name "Test" you can assign it the value "A" like:

    Application.Names("Test").RefersTo = "A"

    Re: Dynamic Range

    In your description you do not say anything about the combobox cboEmplIn. Does this contain the 3 digit numbers, are these loaded from column F, and do you select from this combobox to get the textbox value to change? From the code you included cboInitials has nothing to do with getting the value in the textbox, but cboEmplIn does. The ListIndex property will only give you the index of what is selected in cboEmplIn.

    Re: Which Control Has Focus

    I didn't notice that the control was on a sheet, not a form. ActiveControl only works on a form. I don't know of a way to tell which control has the focus, but what you could try is to use the GetFocus and LostFocus events for the control and set a boolean to tell if the control has the focus. Something like

    Re: Combobox Has The Focus

    Not sure if this will help but you can check to see if combobox has the focus by using the ActiveControl property. Something like

    if me.ActiveControl.Name = "CbFormularios" then

    Re: Conditional Format Result Of Formula

    Also make sure none of the cells are formatted as TEXT. If they are, change the formatting to General and then either edit the cells (go in and get out of them) or if numbers you can copy a 1 and paste special -> multiply. After the cells are in TEXT mode it seems the cells have to be edited in order for calculations to work even if the cell formatting has been changed.

    Re: Fix Vlookup Error

    Try using DATEVALUE instead of TEXT in cell C3.
    Also, the dates in column U are formatted as dates but are not being read as dates. I know this because if put my cursor in cell and press enter the formatting changes. Another way to tell is if I switch the cell format to General it should change to the serial number for the date, and it doesn't. At one time you may have had the column formatted as Text. It would be better to change the cells in column U to real dates that Excel recognizes before working on your formula in cell C3. If you have a lot of dates the quick way is to Copy the number 1 and do Paste Special -> Multiply to column U.
    Also if you are fairly new to the vlookup function you should downgrade your assumed experience in your profile.

    Re: Runtime Error 91 With Find() Method

    What do you want to do if Find doesn't find anything? You are trying to Activate a cell that can't be found and that is why you are getting an error. Depending on what you want to do, you need to add something like "On Error Resume Next" or compare the find return value with "Nothing" before you try to Activate the cell.