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: Copying Data: Application-defined Or Object-defined Error
On the second example change the last main code line to
Worksheets("Transfer report").Cells(4, TCol).PasteSpecial xlPasteValuesOn the first one when assigning values this way the number of cells on each side of the "=" sign must be the same.
Re: Substring
Also the MID command.
ADDED BY ADMIN
Left
Right
Mid
Split
Instr
Instrev
Replace
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: Call Private Sub From Module
You can also use the Option Private Module and not use Private with the Sub so that the sub names will not appear in the macro list that the user can see.
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: Grabing The First Word In A Cell
Assume your text is in cell A1 then the following gets the first word:
=LEFT(A1,SEARCH(" ",A1))
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
scishere,
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
Furion,
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: Dynamic Range
Not sure if this will solve your problem but the blank at the bottom of your list is from your formula and 1 needs to be subtracted from the row height value:
=OFFSET($A$2,0,0,MATCH("*",$A:$A,-1)-1,1)
Re: Inserting
In the second to the last row you are assigning Count (a number) to LastRow (defined as a range). You probably want LastRow defined as a Long.
Dim LastRow As Long ' not RangeRe: Create A Dynamic Named Range Without Blanks
You can get an idea of how to do it here: http://www.cpearson.com/excel/noblanks.htm
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
Public CbFormulariosHasFocus As Boolean
Private Sub CbFormularios_GotFocus()
CbFormulariosHasFocus = True
End Sub
Private Sub CbFormularios_LostFocus()
CbFormulariosHasFocus = False
End SubRe: 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" thenRe: 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.