I think I asked this in a much more complicated & confusing way before; hoping this makes more sense. I'd like to set something up so that when a name is entered in ColA of sheet1, it disappears from a list of names in ColA of sheet 2.
View 9 Repliesmy sheet tabs sometimes disappears when I open excel. I know you can go through the start button and the advanced option to display the sheet tabs. Is there code I can incorporate so that on the opening of the excel file after the splash screen runs its course, the sheet tabs are displayed.
View 9 Replies View RelatedI have a workbook with multiple sheets. I would like to protect both the structure and window, but when I do this the sheet tabs are no longer visible when I reopen the workbook.
View 4 Replies View RelatedThis is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
I have copied my first sheet into multiple sheets. Now when I enter data in one it shows up in all of them. How do I stop this from happening? I have cleared out any content in them and the formula bar is blank in every line.
View 3 Replies View RelatedI found some VBA code here which I very slightly modified that I'm using to bring pictures over top of cells based on their values. The code works great but I can't get it to automatically update the pictures. To get the picture in each cell I have to hit F2+Enter. I've made sure that my calculations are on automatic.
I have tried the text-to-column trick as well as the find-replace = with = trick and neither of them worked. I'm guessing I need to call a function here to force each cell to recalculate in the same way F2+Enter works but I'm not how to do that or where to put it. I tried Application.Volatile True and that didn't work either.
[Code] .....
Please find attached.I have two sheets. In sheet 3 I will enter data. It must record on corresponding date & column in sheet 4.
Book2.xlsx‎
I would like a button in (Sheet2) to do this operation when clicked: jump to (Sheet1) and enter Sheet2's name into cell B4, where this button is used in many sheets similar to Sheet2(aka2,3,4,5,6...) that all do the same to jump to sheet 1.
View 2 Replies View RelatedI have a workbook that has various number of worksheets at any time. Each worksheet has data about current employees. We can have anywhere between 20 and 50 employees at a time. When we get a new employee, we take the "template" worksheet, copy it, and rename it with the employee name. We also have a summary sheet. On the summary sheet we have the employee name in column "A" and the hire date in column "B"
Each employee sheet is set up the same since we use the template. So the employee name is in cell "A6" and the date is in "I6".
Is there a macro I can use that will update the summary page every time a worksheet is added or removed? I don't want the macro to run on its own - I want to make sure we can manually run it (after we are done entering data into the new sheet).
So, basically, what I want to do is to search for all worksheets except "template" and "summary" and take the value in cells "A6" and "I6" and paste it in the "summary" sheet in cells "A3" and "B3" respectively, and then the next values from the next sheet into "A4" and "B4" and so on and so on.
I have been experimenting with code all day, and started just trying to get the employee names copied over (cell A6) and can't do it so I haven't even tried the hire date yet (cell I6). I have tried probably 15 different codes but I guess I do not know what I am doing. The latest code I tried is....
Worksheets("Summary").Activate
FirstCell = Worksheets("Summary").Range("A3")
For Each Worksheet In Sheets
If Worksheet.Name "Summary" Then
ActiveSheet.Range("A6").Copy Destination:=Worksheets("Summary").Range(FirstCell + 1, 0)
End If
Next Worksheet
End Sub
It would be cool too if after all the values have been pasted into the "summary" sheet if I could sort alphabetically, but I don't want to push my luck.
I am starting a new Fantasy Football League. I am wanting to be able to enter players into Sheet 2 as they are picked. And I am wanting those picks to be simultaneously placed on Sheets 3-14 according to their position. Sheet 1 is a list of names according to positions. A more detailed explanantion is entered below.....
View 9 Replies View RelatedI use the same number as a lookup value in several sheets(say 1-10). The number can only be changed in sheet 1, the others referring to it. So if you want to use a different value in say sheet 9 you have to return to sheet 1 and change it, then back to sheet 9. Is there any way that a number can be inputted into ANY of the sheets and change ALL of them, without using macros? I've searched long and hard for the solution to this, I'm sure it's either a trivial answer or a trivial question that doesn't deserve an answer.
View 9 Replies View RelatedI have some code I wrote that copies sheets from another sheet in another workbook to my current one, in order. However, depending on activity within these sheets, there might not be a sheet for the month.
I have the on error code to go to the next argument, however I would like it to tell me on my errors sheet that it did not exist.
The error sheet will simply have 2 columns of copied sheets and non-existent sheet.
VB:
On Error Resume Next
Workbooks("GLTemp").Sheets("981715").Copy After:=Workbooks("MHSGMR").Sheets("981715 Budget")
Workbooks("MHSGMR").Sheets("981715").Activate
sheetName = ActiveSheet.Name
Workbooks("MHSGMR").Sheets("981715").Select
ActiveSheet.Name = sheetName & " GL"
[Code] ......
Enter Data To Multi Sheet Through Userform. How can I enter Data from entryform to multisheet?
View 6 Replies View RelatedFrom sheet1 on a checkbox click I am attempting to post the system date into sheet3 cell c3. If c3 already contains data then I want the cell selection to go down to the next row and post the date there. I if cell c3 has no data then it posts fine but if I need to goto the next row then I get a runtime error "object doesn't support this property or method".
If Me.GCN = True Then
If Not IsEmpty(Worksheets("GCN_Paid").Range("c3")) Then
Sheets(3).ActiveCell.Offset(1, 0).Select
Sheets(3).Range("c3") = Date
Else
Sheets(3).Range("c3") = Date
End If
End If
I have a formula
{=IF(OR(D9=M4:S99),"m")}
For instance
This formula works
But when i click in the cell to edit the formula the {} (at the beginning and end) disapear and the formula no longer works.
If i add them back manually the formula still does not work.
How can i edit these formulas without stopping them working.
I have a formula in C5 = IF(C$1=$A$2,($J$3-B$4)/COUNTA(C$1:$J$1),"") the problem is, when I will copy the formula in C5 and paste it to D5, E5, F5, G5, H5, I5 & J5 respectively, the value will disappear. What should be my formula in order not to disappear the value?
ABCDEFGHIJ108-Mar-0915-Mar-0922-Mar-0929-Mar-0905-Apr-0912-Apr-0919-Apr-0926-Apr-0903-May-09208-Mar-0914-Mar-0921-Mar-0928-Mar-0904-Apr-0911-Apr-0918-Apr-0925-Apr-0902-May-0909-May-093Planned Cumulative1204Actual Cumulative15Remaining Qty. per Wk14.88
I have an excel document that looks all nice and pretty except for the little white grids all over it with unused cells. Is there anyway to hide the lines? i dont care that the cells are there just the lines make the form look kinda crappy.
View 4 Replies View RelatedIs it possible in a Label caption to be visibile in a second?
I just want, when the user hit the commandbutton1, a label will be visible in a second and then disappear again after 1 second.
One of my workbooks won't allow me to use the VBE variable types (VBProject, VBComponent, etc.) They aren't on the popup list that appears when I type " as ". If I just type in "VBProject", I get a compile error "User-defined type not defined". I have to define the variable as object, and the macro still works. I do not have this problem in any other workbook.
View 3 Replies View Relatedbefore i run my macro, i would like a msg box that appears that says "please wait, this may take a while". I've tried using the msgbox function but unless the user presses the "OK" button on it, the macro won't run....
any suggestions on how i can have a userform/msgbox appear while the macro is working in the background (without the user having to press "ok")
Is there a macro that can make cells with no data in them disappear or not be displayed?
View 9 Replies View RelatedIn recent weeks, when I've been asked to demonstrate excel applications which make use of buttons, I've found that on some machines the buttons will variably display as flat grey rectangles with no label, or boxes with red 'x's, or alternatively not appear at all. There seems to be no pattern since the variations have all happened on single machines, then not at all on similar machines.
So far it has only been company laptops (not my own machine), hooked up to overhead projectors which end up with this problem. I can't think of anything I would have done manually to disable the buttons from appearing.
Is this a known issue, or is this simply down to the user preferences and/or security settings?
Why after the first loop (Z=2) all of a sudden column A in WS ATT disappears. Worth adding that column M in WS L is a "dynamic" range (at first it contains 7 cells) in the sense that it shrinks with each loop (M2:M8, second loop: M2:M7 etc.).
[Code] .....
I have a worksheet (in a workbook with many worksheets) where some of the cells display the formula I put in after I leave the cell. I've made sure that the cell format is General before I type anything in the cell. I've inserted new rows, above where the problem is and have tried going to another cell in a different part of the work sheet but I have the same problem. I use the same formulas in two other worksheets. The formulas include other worksheets in them. example =IF(PlotMaster!B2="","",PlotMaster!B2). I can't figure out what is going on.
View 1 Replies View RelatedI've created some code so that when users want to update something on and Excel template I'm creating for them that the information they've put in is backed up. They give the backup a name using an inputbox and it loops through previously loaded backups that I've stored in a separate sheet and checks if the backup already exists. If it does a msgbox appears to ask them if they want to replace the existing copy. If they choose not to, it re-opens the inputbox.
The issue I'm having is that the msgbox doesn't close before the input box opens and it looks messy and may be confusing. Is there any way, without creating a useform, that I can force the msgbox to close once the user has made a choice before the inputbox opens?
Here is the code:
Code:
1
BackName = InputBox("Give the backup a name", "Backup Name")
If BackName = "" Then GoTo 2
For i = 1 To LR1
If UCase(Sheet18.Cells(i, 1).Value) = UCase(BackName) Then
If MsgBox("Backup already exists. Replace?", vbYesNo) = vbYes Then
[Code] ......
I created a macro that prints a label from a userform. The macro used to reside on an XP platform and performed without issues. IT decided they wanted to upgrade the machine to a Windows 7 platform. Now when the userform prints a small window pops up that looks like a print preview of the label, however it is transparent and has a window caption of 'Precut Label' as identified in the userform design. I am including a screenshot of the pop-up. The unusual thing about this is the window cannot be closed, moved or selected in any way shape or form. The only way to get it to close is to view the userform object in the vba editor. The line of code that triggers the print sequence as well as cause the pop-up is: frmLabel.PrintForm. This line of code is housed in the sheet1 object.
View 1 Replies View RelatedI have two option buttons. I would like to have a command button disappear if one option is filled and reappear when the other option is filled. What is the best way to do this?
View 9 Replies View RelatedI have got this in a pivot table
Customer Total sales(USD)
30010 500.00
30041 2000.00
In another column I enter a comment for customer 30010(and leave the comment column for customer 30041 blank) as below
Data 1.xls
Customer Total sales(USD) Comment
30010 500.00 Test 1
30041 2000.00
Later on the content of the file changes as below:
Customer Total sales(USD)
30025 1500.00
30010 500.00
30041 2000.00
When the file is refreshed I would like to have comment as per the former file i.e the comment “Test 1” should appear against customer 30010 and similarly for any other customer that I enter the comment.It should be as below:
Customer Total sales(USD) Comment
30025 1500.00
30010 500.00 Test 1
Customer Total sales(USD) Comment
30041 2000.00
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.
View 3 Replies View Related