I use "ActiveWindow.ScrollWorkbookTabs Position:=xlFirst" to display all available sheets in a workbook. But can the worksheet of the first tab (i.e. at the far left) be automatically selected after the workbook is opened?
View 6 RepliesI seem to running into dead ends with each previous post concerning this similar idea, despite brave efforts of kind people . . .
So here is another thought: As the title suggests, can one help me with code that would take the value in (say) Sheet1A1 (Say Harry) and use that value to search for a sheet with that name (Harry) in the same workbook and then link to that sheet, cell A1 (Harry!A1)?
The other posts are here, should you want to know, but they differ as far as the linking method goes:
Post 1, Title: Newly created Tab Names placed as Hyperlink on Content Sheet
Newly created Tab Names placed as Hyperlink on Content Sheet
Post 2, Title: Automatically redirect hyperlink when cell value changes
Newly created Tab Names placed as Hyperlink on Content Sheet
I could use some assistance in creating a macro that will delete all and only text boxes on the active sheet. Some text boxes will be empty, but I still wish for them all to be deleted. Through searching the forums I see a similiar code, but it deletes all shapes on the active sheet:
View 8 Replies View RelatedI'm having a problem moving the cell cursor 2 rows down once I find my LastRow (see LastRow code below). My problem is I need to move down 2 VISIBLE Rows down and I'm running into it moving 2 Rows down whether they are Visible or Hidden? If Row 50 is my LastRow, and I have it move 2 rows down, if Row 52 is Hidden, it still moves to Row 52 which can't be seen by the user after I do my stuff to that row? I can't unhide any hidden rows for other reasons. Is there a way to have the cursor move down 2 VISIBLE Rows instead of 2 Rows regardless?
The code I use to select the LastRow is:
LastRow = Cells(4000, 8).End(xlUp).Row
I move down 2 more rows using:
Cells(LastRow + 2, 8).Select 'Then I do some stuff here for the user, so it can't be a hidden row
I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.
I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.
The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.
how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?
I have below a code that is selecting from A11 down as far as there is data and across a certain number of columns then sets a print area.
Currently the columns go A, B, JW, JX, JY, JZ, KA, KB as C through to JV are hidden. I have tried to offset the columns by 7 to see if it will select A-KB but it is selecting cells that are hidden and only selecting A-G. I need it to go past the hidden columns. Here is the code:
[Code] ......
Could I also get a code to go from A10 and move over to KB and create a range from there e.g. KB-KJ and last row from there?
I have a macro that allows me to select a range of columns and unhide any that are hidden. I am on the sheet "Employees", but the action happens on the sheet "Enter". I can run this macro when I am on "Employees" and jumps top "Enter" and works exactly as I want it to.
Code:
Sheets("Enter").Select
Columns("B:T").Select
Selection.EntireColumn.Hidden = False
Range("M8") = Sheets("Employees").Range("B5")
Range("I7").Select
But when I try to run it as part of another macro that begins on "Employees", I get "Run-time error '1004': Select method of Range class failed", and Columns("B:T") is highlighted.
Ok I recently ran into something in Excel that was completely unexpected. When I filter data, and select multiple rows of data, and copy and paste it.... it pastes the unfiltered rows as hidden rows! Is there a way around this without copying row by row?
View 1 Replies View RelatedI have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.
Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.
I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.
Does anyone know what might be causing this, or is the workbook corrupt?
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:
1-the sheet is renamed to the value of F1,
2-a new tab is made (a carbon copy of the hidden sheet "Template")
3-the new tab is named "New Tab" and marked as unhidden.
Public Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("F1").Value
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function..................
I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
I have a userform which clones the latest sheet and produces copy of it on the next sheet. The first sheet they will be cloning is the sheet called 'template', I however want this to be hidden since I do not want anybody to modify a sheet which basically serves purpose of a template. And once cloned, it is no longer needed.
On the click of the command button, the macro will create sheet1 taking the info from sheet named 'Template' in the same workbook . And now on the next click of the command button, Sheet 2 is created taking the info from sheet1 and Sheet 3 is created taking the info from Sheet 2 and so on. Here is what I currently have, so how can i modify it in order for my scnerio to work?
To select the last sheet in the workbook
Code:
Sheets(Sheets.Count).Select
To create new sheet
Code:
Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = MyEvent & " " & MySCN & "(" & ThisWorkbook.Sheets.Count - 2 & ")"
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.
Sub UnhideSheet1()
Sheet2.Visible = True
End Sub
I'm able to export hidden worksheet to PDF. Currently, I have the follow code below which will export active worksheet.
Code:
With Worksheets("ExportTable")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
After I hide the tab, when I tried to export it. I have an error message of "Invalid argument". I should use another type of code to export hidden tabs.
Is it possible to protect a hidden worksheet. I do not want them to be able to unhide the sheet unless they know a password to do so.
View 9 Replies View RelatedI was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.
Macro command used:
Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"
My workbook contains 10 worksheets. Some users will use all 10 worksheets; some will use only 5 worksheets. If a user is not using a worksheet, I would like to hide it but leave it in the workbook.
However, I want to keep all workbooks in sync even though a user may choose to not use a particular worksheet. When my macros encounter a hidden worksheet, they stall. Does coding exist that allows a Macro to run on a hidden worksheet without making the worksheet visible?
Excel 2003. Windows XP Professional. Bank reconciliations. How is it possible in an unprotected worksheet to hide additional data input in formulas so that visible invalid numbers produce accurate results? Displaying hidden formulas in formula bar reveals nothing. Blank cells have been included in formula, but searches for hidden numbers and links in these cells produced nothing. I'm at my wit's end to correct this misuse of Excel in my office.
View 14 Replies View Related1) I cannot figure out how to write VBA so the "Show Results" button will open the hidden worksheet when clicked.
2) When I use the userform to add new info, it does save to the worksheet, but when I cose the userform and try to open the spreadsheet, I get the following message, "Userform is already opened. Reopening will cause any changes to be disguarded". If I answer yes, I lose my changes, if I answer no, the changes are saved. I want this message to go away and data from the userform to automatically be added to the spreadsheet.
3) I want to make sure the "Account Number" field has a 9 digit number in it before it can be saved. If there is not a 9 digit number I would like an error message saying "Please enter a 9 digit account number" as a pop up.
4) I want to make sure both the "Account Number" and the "Assignment" fields have been populated before the data can be saved.
saw the other posts about hidden worksheets and couldn't get my makro to work. The macro works fine when the sheet in question is not hidden but when it is or when I tried to solve it with
Sheets(" total").visible = True
.
.
.
Sheets("total").Visible = False
Why could this be? The problem only occurs when the sheet is hidden or when code like the above are used, what could be done to fix the problem.
I have a button on a sheet that I want to unhide another sheet, but if this other sheet is already visible then I want this same button to hide it.
Here is what I tried and it does work except it runs all the IF's instead of stoping at THE IF that matches the condition.
Sub HideUnhide()
If Sheet2.Visible = xlSheetHidden Then
Sheet2.Visible = True
End If
If Sheet2.Visible = xlSheetVeryHidden Then
Sheet2.Visible = True
End If
If Sheet2.Visible = xlSheetVisible Then
Sheet2.Visible = False
End If
End Sub
I have a spreadsheet which I'm using as a log-in front for a series of other workbooks. The usernames and passwords are stored hidden cells within a "very hidden" worksheet. The workbook is protected and VBA code itself is password protected.
In short no-one can directly view the passwords (unless the password to unprotect it all is known.
What I have found is that someone can use a formula to reference the cells containing the user details. "=a1" for example.
Is the a method hiding the contents of a cell from excel itself? I want the vba script to be able to see the value, but any "=a1" formulas to return a blank.
I need to create a new excel template for work. I was planning to copy a few worksheets from an old workbook over into the new workbook, mostly just for formatting for some graphs/tables that I would then link to the new workbook. A coworker said I cannot do this as copying from an old workbook will bring in "hidden" data that could some way impact the integrity of the new workbook and that the copied/old worksheet would FOREVER be linked to the new workbook no matter what I do. Is this true? If so, are there things I can do to remove any reliance to the old workbook.
View 1 Replies View RelatedI asked for a macro that copy an worksheet and rename it using a userfom!
I received the xls file attached to this mail !
In this xls when I click "Click me to copy s0 sheet and rename it" from final sheet, a userform appear, I type a name there and when I press ok I will have a copy of worksheet "s0" with the name typed in the userform.
If I click on "Click me to copy s0 sheet and rename it" I can create as many copies as I need.
But if I hide worksheet "s0" (using Format /Sheet/Hide) the macro will not work.
I modified the code from module 1 adding the red
I have an excel document displaying graphs that i would like to link to from a menu page (via a number of buttons). The problem is that i have about 12 of these graph sheets (along with 8 other sheets) and i'd rather not have them cluttering up the worksheet tabs list at the bottom of the screen.
Does anyone have any suggestions about how to handle this? Is a macro to open a hidden sheet and then close it once it is navigated away from a reasonable option? Or does Excel protocol suggest that they should remain visible, despite their cluttering..
I've developed a workbook that is designed to allow users to produce a list of aspects (one per worksheet) and edit them as the aspect changes. However, i would very much like to create a 'log' of changes. For example, everytime a cell is changed the cell reference, worksheet reference and date are added to a list on a hidden worksheet.
View 9 Replies View RelatedI'm trying to access/read data from a hidden WorkSheet in Excel 2003 using:
Application.Worksheets("Hidden Sheet").Activate
With ActiveSheet
**** Data ****
End With
The accessed **** Data **** refers to another ('unhidden') WorkSheet. How can I read the data without making the WorkSheet visible to the User?
Ok, how to select next cell to right (2) after input (1). not down as default.
[URL]....
7ZBgdcQ.png
I have 3 columns, A1, A2 and A3
I would like A3 to show me the value of A1 unless there is a value in A2 then it would show the value of A2 instead.
I would also like to change the text color to reflect which cell the value comes from.
I have a range c10:c300, i want to select the range where i have data.
E.g. I have data in c10:c50, then select the range c10:c50