i'm using Sendkeys Edit Find to open the Find dialog box (works well)...i would like to restrict the searching to only 4 worksheets (of 14) of the workbook...?
(i see the option to select Sheet or Workbook)
I press Shift and click on the selected sheet tabs, goto Edit> Move or Copy Sheets... the dialog box allows move or copy to a new workbook or to an open workbook. Macro doesn't record syntax for the Dialog box. This build-in function also doesnt allow me to selectively choose the worksheets in the active workbook.
I am looking for this code or a sample dialog box as I need to incorporate it as a part of my task automation macro. I would like a dialog box that allows eg. listbox to choose the individual sheets, then with option to Copy only (collectively Copy or Move are also fine) the selected sheets to a new Workbook or an already open Workbook. I am using Excel 2003.
I want to show the insert symbol dialog through vba code. How?
View 12 Replies View RelatedI am trying to programatically access a built in excel menu to edit the font dialog for text boxes. Specifically, I want to access the diolag box which opens when a text box is selected and you click the arrow in the bottom right of the font group in the home tab (Excel 2007). I do not want the dialog box which opens when just a cell is selected. The dialog I want has two tabs - font and character spacing. I know how to use application.dialogs().show to open built in dialog boxes in VBA, but I can't figure out how to show this specific dialog.
View 3 Replies View RelatedI'm having some trouble showing a messagebox if the user presses OK but doesn't select a folder using msoFileDialogFolderPicker.
[Code] .....
The 2 MsgBox's I've added show the same string, yet the If Not InStr line returns true regardless if ":" is in the string or not.
I have a workbook with 6 tabs. The workbook is designed to be shared but I want to password protect the last 4 sheets so that they can only be accessed by certain people. The people who have access need to be able to edit the sheet.
View 8 Replies View Relatedwhat i need is this code to find the data only in N11:N22 i do not want the data to be search anywhere esle ...
View 9 Replies View RelatedIn the routine:
Private Sub bttnPCsFind_Click()
Application.ScreenUpdating = False
Cells.Select
ActiveWindow.ScrollColumn = 10
Application.ScreenUpdating = True
Application.Dialogs(xlDialogFormulaFind).Show
Application.ScreenUpdating = False
ActiveCell.Select
ActiveCell.Select
ActiveWindow.ScrollColumn = 10
End Sub
Is there a way to set the range of cells for the DialogFormulaFind to search against that doesn't involve Cells.Select? Functionally the above routine works perfectly but, if given the option, I would prefer to not have the cells highlighted (and thus darkened) while performing the search. If I remove this line altogether then nothing in the search is ever found. It is a visual glitch I am looking to get around.
Does anybody know how Excel manages the selection of the cells when performing a find using Ctrl + F (when performing a find using the hot key or menu item the cell range isn't selected (highlighted) ahead of time and thus, remain white during the search/Find Next). Is this possible to code this behavior using VBA?
I'm looking to have a Button on my sheet that will open the Find box so people do not have to go to the menu to find it. Is there a code that can open this?
View 2 Replies View RelatedRelated to my thead here, is there a way that the Find dialog can be automatically closed after the find?
View 7 Replies View RelatedWhen searching the 'Look in' contains 'Formulas' by default, how do I change that so that the default is now 'Values'?
View 3 Replies View RelatedI have the following code in a macro to open up a find dialog box, but it does not seem to work. I am getting the following message when I try to find something:
Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exist in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected sheet.
I checked the data I am trying to find and replace and it is correct.
I have Excel 2010, and a dual monitor setup, with the left monitor being the main one. I have my spreadsheets on the right monitor.
When I open a Find and Replace, the dialog box always goes to monitor #1 (left); I want it on monitor #2.
Is there a way to default it to the "right" monitor?
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
View 4 Replies View RelatedI'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code: [URL]....
I have 1 source file that shows all the changes we made to the following 14 spreadsheets; essentially a change log.
I have 14 excel spreadsheet...the last tab in each of them have an object (the source file) captured, so they are all identical
Lets say the 14 spreadsheets are Makes of cars...(1 for Dodge, 1 for Ford, etc)...each time we make a change to the car, I log that change in the source file.
Lets say I have 33 things for dodge, 17 for Ford, 39 for Lexus, etc...all in the change log. Is there any possible way for the final tab of each of those docs, to ONLY show the changes that are specifc to that model?
My actual scenario is different, but this is the same concept and much easier to explain. If this is possible, I'd like to go a step further to know it is can be linked to the source file so it updates upon opening just like the object does now.
For what its worth, you can key off of column A in the source file....that is essentially my "Model" column.
I downloaded a cool add-in from XL-Logic that lets you select sheets to print. It's great, except that I need to print very hidden sheets and it does not display very hidden sheets. Does anyone know how to modify this code so it will list all sheet including very hidden sheets? And is there a way to make the sheets appear in alphabetical order? I've tried to play with it, but I don't know enough about VB....
View 9 Replies View RelatedI have 15 sheets - Resident 1, Resident 2 etc
If cell A1 on Summary tab contains 10, I want sheets Resident 1 to Resident 10 visible, everything over Resident 10 to be invisible
If cell A1 on Summary tab contains 5, I want sheets Resident 1 to Resident 5 visible, everything over Resident 5 to be invisible
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
Me.Save
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True
End Sub
With Excel i'd like to compare the stock of about 6000 different products from my supplier on a daily base. I want to know the changes in the inventory (indicated by Yes, No or Low). Is it also possible to show newly added/ removed products?What is the best way to do this?
View 1 Replies View RelatedI got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.
View 14 Replies View RelatedCan a macro be provided to show the list of sheet names in a drop down list at cell B2?
View 9 Replies View RelatedSub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub
It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.
I made one file with 13 sheets.
sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ
2. abc
3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
compare data from different worksheets
For clarity, I have placed the sample datasets at the following URL
[url]
Given: the datasets for sheet1 and 2 might not contain the same number of rows.
I am looking for new rows (it might be inserted in any position of the rows) in sheet1. The new rows will be highlighted in red color. For example:
[url]
I have an excel spreadsheet that has an instruction page for the first worksheet. I have included screen shots for clarification. One of the screen shots is of the Find/Replace window. Sure enough, when people started looking at this sheet, they tried to click on the picture of that window to use it. I would like to make it so when they click on this picture they either a.) Open that command window or b they can start using it right there and not be able to move it so the next person will always be able to use it from the start.
View 3 Replies View Relatedi want to ask if are some way to do it , to show 2 sheets in 1 sheet , because what i am trying to do is , with VBA copy table with pictures from database sheet to printing sheet , add blank columns and create another table in that blank area , and got this problem that formating goes crazy , because each table need different row heights. Maybe there is any way to copy my database sheet and paste special that it don't cares what i will do with row heights?
View 6 Replies View RelatedI have a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia
What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want
1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C.
2. Basis the range I want sheets to show or hide.
I have a workbook with 50 sheets. Within each sheet are rows of text that I need to search for specific text.
I'd like to search each row from each sheet for specific words (e.g. "7 days") then show the resulting rows data/text in a new sheet along with the name of the sheet and row number it came from.
e.g.
Sheet 1 contains the text "7 Days" on line 40
Sheet 2 contains the text "7 Days" on line 1, 30, 50
In a new sheet, I would show the entire row in its own row that contains the text "7 Days" along with the name of the sheet and row number it was found in
I imagine this is pretty simple but for the life of me I can't figure it out. Here's what I want to do...
Find MIN in Column F
After finding the MIN in F (let's pretend F222 is the current min)
Show the text in the field next to it (in this case display text from E222)