Show Text In Active Window Caption
In an open excel file, in the top left-hand corner, the file name is always "Microsoft Excel - Your File Name".
does anyone know how to have the top-left hand corner show "Your File Name" only?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Active Window
I've built a macro to run a loop that opens excel files, prints certain sheets and closes them. I have the macro working - to some degree. here is my code (this is excel): Sub RMPProducer() OldPath = "S:RMBS_Performance_Analytics Analysis1 Staging Folder For Monthly Model Templates2007200704VVDeals" Dim t As Workbook Dim s As String Dim a As Window With Application.FileSearch .NewSearch .LookIn = OldPath .SearchSubFolders = False .Filename = "*.xls" .MatchTextExactly = True If .Execute() > 0 Then For i = 1 To .FoundFiles.Count s = .FoundFiles(i) I have 2 problems. First problem is that I am trying to add a function to make the window close once its finished printing. This actually works - but it limits my loop iterations from i = 1 to 2 (so it only runs 2 times and I'm not exactly sure why). If I don't include the statement - this can run for up to 7 - 8 times (these are very large files) then excel runs out of memory. Unfortunately, i can't set the loop to run from i = anything other than one. So What I would prefer is to be able to make the "ActiveWindow.close" command work - because this way I can run the entire loop. But I'm not sure how to. I tried window referencing, I tried adding a new active Window so when the file opens, its opening in the active window already, but nothing works. Preferably, I need to be able to reference both windows, as my next macro will involve referencing 2 windows (one static - always open) and the other the loop above.
View Replies!
View Related
Select Previous Active Window
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done? Sub AutoSave() dTime = Now + TimeValue("00:15:00") Application .OnTime dTime, "AutoSave" Windows("data.xlsm").Activate Sheets("Data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV ActiveWorkbook.Close Application.DisplayAlerts = True Windows(1).ActivatePrevious End Sub
View Replies!
View Related
Show Range Of Cells In Label Caption
I'd like to: Display the contents of a group of cells in a userform's label's caption. The columns containing the cell's which contents would be displayed are fixed (do not change). The rows will change. I need to reference the Ar, Br and Cr columns (where r is for row) of whichever column I am working on at a given moment.
View Replies!
View Related
VBA Grab Active Window Index
Dim iStartBook As Long, iNewBook As Long iStartBook = ActiveWindow.Index ActiveWindow.NewWindow iNewBook = ActiveWindow.Index I'm trying to grab the "2" that .NewWindow generates. The above gives 1 both times, despite the obvious 2 in the caption after you run it. So what property works? What does .Index mean here?
View Replies!
View Related
Conditionally Show/Hide Columns & Change Button Caption
After scouring the entire OzGrid, I have found the following two macros that partly satisfy what I want to achieve. BTW, following two macros are executed on Sheet2 and I want to achieve the following: On Worksheet_Activate, show columns that match the criteria value in Sheet1.D4 (Sheet1.D4 contains a dropdown list with about 6 text values). In addition to that, if Sheet1.D4 value is blank then show all columns. Right now, it successfully shows columns that match the D4 value but does not work if that D4 value is left blank, instead it hides all columns in range (C:CV). So in short, to the first macro, I need to add the criteria if D4 is blank, then keep the sheet intact and don’t hide anything, just show all columns.Once on that sheet after the Worksheet_Activate event has occurred, with the second macro “ToggleColumnsVisibility” following things need to happen. BTW, currently this macro is assigned to a button (from Forms Toolbar).If cell D4 has a value, then the button’s caption should read “Show All” and when that button is pressed, it should show all columns in the range (C:CV), and the button's caption needs to change to “Show Selected”. In “Show Selected” mode, when that button is pressed, it needs to show all the columns that match the D4 value.If cell D4 is left blank, then the button's caption should read "No Action" and when the button is pressed, it should not do anything but just show all columns. As you will notice in the header, my experience with Excel is very limited, so detailed instructions will be greatly appreciated. I am also hoping that proposed solution uses some of the efficient/effective ways of using loops such as described below (example taken from this website from this link http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm) Sub TrueOrFalseFaster() Dim bYesNo As Boolean Dim i As Integer bYesNo = (i = 5) MsgBox bYesNo End Sub Sub ToggleTrueOrFalseFaster() Dim bYesNo As Boolean bYesNo = Not bYesNo MsgBox bYesNo End Sub .........
View Replies!
View Related
Show Window Option For Shared Workbook
Everytime I open a Shared Workbook my OPTIONS get changed and my excel tasks in my start bar and consolidated. Is there a way to prevent that? As of now I have to keep going through Tools>Options>Show Windows in Taskbar Macro's aren't allowed in shared workbooks - so what are other options? I would want the same for anyone who opens the file, so a local fix on my machine won't be enough.
View Replies!
View Related
CommandButton Caption Text Clipped
I have a commandbutton who's caption text is getting clipped along the bottom when I view the sheet at 100% zoom. If I look at 200%, the text is fine. What's going on here? I've included a pic of the offending button. Note the missing bottom part of the "g".
View Replies!
View Related
Go To Sheet Named As Text In Button Caption
There is a 'Home' page with a button for each employee - clicking the button takes the user to the appropriate employee's timesheet There is also an 'Add New Employee' button on the home page which brings up a form and allows a new employee to be added. Each employee has only 1 worksheet (Which is created based on a blank template page) When the 'Add New Employee' button is clicked a form pops up which ask for name, D.O.B etc. The user fills out the form and clicks ok. The template sheet is then copied, renamed to the employees name, and all relevent fields are added to the template sheet (based on the users input). A button is also generated on the home page with the employees name as a caption and when it is clicked it goes to employees timesheet. I have almost got this working. The only problem is this only works once, So For Example say i Add 'John' then i add 'Mary' on the home page i now have 2 buttons with 'John' and 'Mary' on them. The problem is as soon as a second employee is added (Mary), the first employees button(John) is linked to the second employee's Sheet rather than its own(Mary). and after the spreadsheet is closed the buttons dont work at all. The link below has the spreadsheet i have made so far, you can test it out if my description doesnt make sense or you can see the code behind the 'add new employee' form: ############################################################ #### 'Add Employee' Button on Add New Employee Form #### ############################################################ Private Sub cmdadd_Click() 'check for Name If Trim(Me.EmpName.Value) = "" Then ..............................
View Replies!
View Related
Show Cell Value(text) In Comment Box Text, Or Mouse Tool Tip On An Gif Icon
I have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value. I've look the properties of this to objects and can figure it out.
View Replies!
View Related
Macro To Only Show Rows That Contain Specific Text/Chinese Text Issue In Visual Basic ( 2007)
I want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.) My text options are: High ¨‚ Medium ’†“™ Low ’į The text arrives in the cells via a VLOOKUP Is the chinese text a problem? i can't type it into VB. I've been using the following macro to hide and unhide rows with a command button in the same sheet: Private Sub CommandButton1_Click() Toggle_Hide_Unhide End Sub Sub Toggle_Hide_Unhide() Dim rngCell As Range Dim TakeAction As Boolean If ActiveSheet.CommandButton1.Caption = "Hide" Then TakeAction = True ActiveSheet.CommandButton1.Caption = "UnHide" Else TakeAction = False ActiveSheet.CommandButton1.Caption = "Hide" End If For Each rngCell In ActiveSheet.Range("I9:I30") With rngCell If .Value = 2 Then .EntireRow.Hidden = TakeAction End With Next rngCell End Sub
View Replies!
View Related
Unload Active Userform And Show New Userform
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Application.ScreenUpdating = True Unload Me RestrictedOptions.Show Else Cancel = True End If End Sub Trying to use above code to unload active userform and show new userform when red X (close) selected by operator. With the code as is, the Unload Me leaves the form on the screen and displays the RestrictedOptions form. If i remove the Restrictedoptions.Show, the Unload Me does remove the original form.
View Replies!
View Related
Replace Text On Non Active Worksheet
Replace specific text within a defined range without having to select the sheet. I tried the following but this is obviously not the way to go. Sub Open_Calls_Rename_Organizations() With Sheets("Open Calls").Range("Organizations") Cells.Replace What:="Institute Technology Code", Replacement:="ITC", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End With End Sub
View Replies!
View Related
Force Text To Uppercase In Active Cells
To ensure correct data entry, I need to ensure that the filled cells in a column are always in Uppercase for example, irrespective of whether they are typed in upper or lower case. I have found the following code, which seems to work quite well, even though I have the impression that it slowed my workbook down slightly.
View Replies!
View Related
Active X Text Box Multiline And Bolding
I have a couple of active x text boxes that I am using in a user form to enter data to a sheet. I wasn't able to get the text to move to the next line, but I changed the Multiline and EnterKeyBehavior to True and now when I hit enter it is working how I want it to. But when I click ok on my user form it is putting the data where I want it but it is putting a symbol that looks like a ? with a border around it in where I am pressing enter. Is there a way to avoid having this symbol show up? The other question I have is if it is possible for specific text to be Bold in the text box and some not? I haven't been able to figure out a solution for that.
View Replies!
View Related
Order For The Text String To Turn Into An Active Formula
When building complex and long formulas in excel which can not be auto filled due to non progressive variables I tend to combine several cells containing parts of the formula using the ampersand (&) operator. E.g. B2=[A1&A2&A3&A4] where: A1=[=] A2=[INDIRECT($A$1&"!"&"S] A3=[8] A4=[")] The result will then look like this: =INDIRECT($A$1&"!"&"S8"), then I copy all the values created by this method (it could be several thousand) and past them into the appropriate worksheet using: past special > past values. The problem is that in order for the text string to turn into an active formula I have to go into each individual cell (F2) and hit Enter. When I am working with thousand of cells this is not very feasible.
View Replies!
View Related
Find Text Based On Active Cell Contents
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View Replies!
View Related
Change/Modify/Add Text In Shape On Non-Active Worksheet
I have a button on say, sheet1 with text that I want to change after a certain action takes place; however, I was trying to change this text without switching sheets. (switching sheets isn't a big deal, I'm mainly trying to do it my way for the practice). I'm able to select the button on the other sheet without actually switching sheets, but when it comes to changing it's text I get an error. Here is the code I tried: Sub macro1 () Worksheets("sheet2").Select Worksheets("sheet1").Shapes("Button 2").Select Selection.Characters.Text = "Done" End Sub Problem with this is it gives cell A1 on sheet2 the "done" text. I also tried this...but it gives the error. Sub macro1 () Worksheets("sheet2").Select Worksheets("sheet1").Shapes("Button 1").Select With Worksheets("sheet1").Shapes("Button 1") .Characters.Text = "Done" End With End Sub
View Replies!
View Related
Show Text Only
I want to get only the amount in a cell that contains date, name, and amount sample: in A1:A2 1970 Mario Gonzalez $700 1973 Mario Krupa $500 i want only the amount of Gonzalez to be display in b1 (or any cell beside that "Gonzalez" name), and if not Gonzalez then no text to be display.
View Replies!
View Related
Show Numbers Not Text
I have over 2000 cells like the following S304 S205 S204 S306 S207 and so on Is there anything I can do to show just the numbers and not the S at the start without me having to delete each S individually?
View Replies!
View Related
Filter To Show Only Text
i am using e2k and i have got to filter some data where the filter relevant data is in one column formated as "standard". it contains either only numbers or letters mixed with numbers. in case of a mixed cell the first symbol is always a letter. i need a way to filter only those rows which contain a mixed cell. the filter criteria should be something like !=#*
View Replies!
View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
View Replies!
View Related
Show Text Box On Chart
I am trying to add the date my charts were created. I have the date the charts were created in a cell then I added a text box to each chart referencing the cell with the date. On some of the charts the date is showing. But on other charts on same sheet, the date only shows when selecting the chart area of the chart but disappears when selecting other charts or cells on the same sheet.
View Replies!
View Related
Show Only The Number Of A Cell Containing Text And Numbers
Lets say cells A1 to A5 contain these lines of information: Till 174 (T0215) - till keeps turning itself off. Till 245 - stuck on windows screen 116 - keyboard is unresponsive Berkel Scale is constantly beeping ped not reading cards Till 156 How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.
View Replies!
View Related
Show A Message When Cell Text Is Changed
In a range of cells (e.g. B26-B40) I have names (first name and second name or first initial and second name). This data is carried from sheet 1 to the second sheet via formula. Some cells may be blank as well. A VB code to display a message when the user changes the name in any of these cells with data or adds a new name to a cell that is blank, in sheet2
View Replies!
View Related
Custom Number Format :: To Contain (and Show) Both An Interger And Text
I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell. Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell. a procedure for format ing the cells in the row?
View Replies!
View Related
UserForm Text Box To Show Range Data
I have created a User Form with required controls to add data in a worksheet and it works fine. Now, I want to retieve data from the hidden worksheet and to view the data in one or two text box controls by clicking a command button placed on the user form. I expect the user to use the user form only.
View Replies!
View Related
Toggle Button Caption Between On/Off
I need a macro for button1. The name of button one is "on" when clicked I want it to put an "X" in A1 and then change the button name to "off" When clicked again I want it to delete the "X" in A1 and change the name back to "on". When clicked again it repeats the same process.
View Replies!
View Related
Find Text, Go To Found Cell & Show Message If More Than 1 Occurrence
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro. My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like. Here is the code I presently have for this part of my macro. Sub GetOpenFileName() strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file") If strFind = vbNullString Then Exit Sub With Sheets(strSheetsMainCompProfile) If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then MsgBox strFind & " cannot be found on this sheet" Else
View Replies!
View Related
Set Text & Hide Control On UserForm Show/Load
I'm trying to do is set up a form load event to initlize some controls. Here is my Private Sub Form_Load() 'Initialize the form lblProcessing.Visible = False txtFileName.Text = "Enter a file name" End Sub At the moment, this event is not triggering. I have the code in the code behind my form - should it be in a module? PS: This site is great - it's answered a lot of my other questions so far without me having to make any posts.
View Replies!
View Related
Form Button Caption Not Showing
I have used the Forms toolbar to create a command button on a chart. However, the text for the command button is hidden. If I do an "Edit Text" on the button, it shows up, but otherwise the button appears blank, even when selected.
View Replies!
View Related
|
TRACKER
