I've been using this code to make menus for all my Excel sheets since NateO made it back in 2003.
- The workbook has 370 sheets, 365 of them are one day per month with the tab named ddmmmyy.
- The Main Menu in this file puts the months Jan, Feb, Mar, etc in cells A3:L3.
- In the single cells under each month it put the month day tab name (ddmmmyy) and hyperlinks it to the sheet tab.
I would like to do two new things.
1. Put the name of the month in everyother cell in row 2, so Jan would be in A2, Feb in C2 and Mar in E2, etc.
2. Put the day of the week for each date in the cell to the right of the date. So the column heading would be Jan in A2, 01Jan08 in A3 and Tue in B3.
I thought I could make another macro and just insert the rows after the Main Menu was built and then VBA copy/paste TEXT(cell,"ddd") down the ranges, but thought there might be a better idea out there...
Sub Workbook_TOC()
Dim ws As Worksheet
Dim wsNw As Worksheet
Dim z As Integer
I have a need to insert formulas into a column using vba. I need to skip a row, when inserting the formulas, so a simple drag using vba would not work without some modification.
Here is the formula, that I need to insert... into column "BJ"
Example Cell BI11...=SUM(BH11-BH10)
Cell BI13...=SUM(BH13-BH12), the next cell needing a formula would be .. Cell BI15...
The formula would be inserted, based on the last row used in column "C".
click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?
what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
spliting of main data By column criterias which start from column E1:L1 something like Pivot Table in new worksheet, but based on formula functions.Each worksheets have to based in one of this column D1:K1 headings. That also have to rapidly change with main data table.
I have a spreadsheet with data in 4 different sections with a summary section at the top. Data titles are in column A with the data to the right, arranged by month.
I have created macros to sort the data in each section. Occasionally I need to add a new row to each section for a new data set. When I do this, it screws up my sorting macros for the sections below. It will sort the same rows that it sorted before, not taking into account that the data is now one row lower.
I need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
Q1. How can we insert new rows in a protected sheet?
For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.
Or any way to address this issue via some other method like adding a scrollbar,etc.
I am working on a project of 4000+ entries. We will be identifying the number of seasons in a year. We would like to add a row in another worksheet for each of the seasons identified.
So say we had data in row 1 of worksheet 1. That row stated there were 3 seasons. Is there a way to automatically insert 3 rows into worksheet 2?
I would like to prevent the user from inserting rows. I know that ater versions of Excel have built in "Protection" features which would easily accomplish this. Unfortunately, our company uses Excel 2000.
I have keyed in a constant value in cell B100 ("bottom" of the practical range). I have tried ...
Private Sub Worksheet _Change (ByVal Target As Excel.Range) MsgBox "Range" & Target.Address & "Was Changed." If Range ("B100").Value = "" Then Application.Undo MsgBox ("Do Not Insert or Delete Rows") EndIf End Sub.
The problem with the above is: The "Change" macro does not seem to detect the insertion of rows. It does, however, detect the deletion of rows.
Is it possible to make it so that a user cannot delete or add sheets to a work book, except through buttons assigned to macros? I've tried simply locking them, but these sheets need to be accessed to input and manipulate data. I have the macros necessary to delete/add sheets, but right now they are redundant since the user can simply do it manually.
I need to insert an arrow in a sheet, in a location and a direction which can be controlled by me (I don't mind hard coding the location and direction), based upon a cell value being positive, negitive or 0. I realise that it will require some code but can't find any infomation on doing anything like this through google or in the various VBA texts I own.
In Column A I have product names. Each product name in column A is followed by several rows of data related to that product directly below it. The number of rows following any given product varies and is also dynamic as rows of data are added to other products above it.
I want a simple downdown menu in A1 that is populated with every product listed in Column A. When a product is selected, that area of the worksheet showing that product is shown.
I have a command button that runs a macro to insert two new rows between row 15 and 16 no real problem (see code & sheet below), but I would like the Total Hours and Total Cost rows to include the newly added rows i.e.
Rows 16 & 17
Cell B18 now contains =B2+B4+B6+B8+B10+B12+B14+B16 and Cell B19 now contains =B3+B5+B7+B9+B11+B13+B15+B17
If another two rows are added then Total Hours and Total Cost Cells 20 & 21 will now be
Cell B20 now contains =B2+B4+B6+B8+B10+B12+B14+B16+B18 and Cell B21 now contains =B3+B5+B7+B9+B11+B13+B15+B17+B19
Command button Code to insert row
Code: Private Sub CommandButton1_Click() Const fWhat As String = "EXTERNAL" Dim sR As Range, fR As Range, fAdr As String, nRw As Long Set sR = Range("A1:C187")
I'd like to insert a picture background for multiple s/ sheets I've developed, however, even though the images I'm using are only 30kb's in size, it bumps my s/sheet file size from 900kb's to over 4mb's ! Is there a way I can stop this from happening, or, any way to restrict the background image to only a certain part of the worksheet as opposed to the entire thing?
I have a workbook with multiple sheets. Is there a tool that creates a menu bar or code that I can add that would support navigation?
My goal is to have something with approximately 4 options (buttons?). They would link to 4 different sheets that serve as indexes within the workbook.
I know I can add a control like a button to a given sheet that would take the user to given sheet when clicked, but can I make the button 'float' above all the sheets, or add to a menu bar, or something? I just don't want to have to add the button to every sheet in the workbook.
I am looking for something simple/easy, and am not looking for anybody to do the work. I just have no idea on how to approach the problem.
When a user clicks a cell in Column A, VBA will enter the date in the cell (in the form MMDDYY) and will position the insertion pointer at the end of the date. The user can then enter a four digit number following the MMDDYY. In this worksheet, Column A holds a transaction number, which is always in the form MMDDYYXXXX. I am trying to save the user the trouble of entering the MMDDYY, since it will always be today's date.
I have a Workbook with about 20 Worksheets, for each sheet I have a Selection Change Event that simply performs the Following:
MainSheet1. Range(“Z1”) = Now
1 Cell on every Sheet then is pointed to this cell via =Main!Z1. Problem is that when this is preformed Each Row from the Other In-Active Sheets and sometimes a near-by Button will Appear on my Active Sheet. I cannot select the parts of the sheets that appear and when I scroll down till they are out of view and then scroll back up they are gone until I select another cell and it happens again. I have tried in the Z1 cell to just put =NOW() and Make Calculations Manual and then with the selection change to call MainSheet1.Calculate or Workbook.Calculate but this has the very same effect and the other sheets bleed onto or ghost onto my Active Sheet. So far the only way I’ve been able to get around this is:
If Application. ScreenUpdating Then Application.ScreenUpdating = False MainSheet1.Range(“Z1”) = Now Application.ScreenUpdating = True Else MainSheet1.Range(“Z1”) = Now End If...............
I need to insert text at the curson position in a text box on a VBA form. I know how to make the button not take the focus; I just need to know how to pass the cursor a timestamp.
I have a column with various drop down menu boxes and I need to add another selection to the menu list. My problem is, it's been so long since I did the drop downs, I have forgotten how to do it..
I'm adding a menu selection to the right-click menu that you get for cells. I can add and remove the menu option but, when I try to execute the macro tied to the menu, I get "the macro...cannot be found". I can't figure out what appears to be a "pathing problem". I'm sure it's simple but...
Now this is something which I dont even know if it can be done, but its something which I would use, a lot. When clicking on a button, like a normal button from the control toolbox, is it possible to open a menu from this? So I click on the button i made called "accounts" and this opens a menu next to it with different options.
my worksheet menu bar is displaying the ' Chart' menu no matter what I do. Not only do I have no charts in the workbook, (verified this by: )
For Each ws In Worksheets
ws.Select MsgBox ActiveSheet.ChartObjects.Count
Next ws
but adding worksheets, selecting various parts of a worksheet, creating a chart and deleting it, and everything else I've tried has no effect.
One interesting thing - the first chart I added (to test if it would 'unstick' itself upon deletion of the new chart) was named 'Chart 2', implying there was a chart1 that existed previously.... although I am fairly certain I never added a chart to this workbook at any point.
Also, the menu bar is working fine in other workbooks, and changes to chart and back to data like normal.
Suppose I delete a (unknown) number of items from the Worksheet Menu Bar. This can vary because of the fact that my excel sheet is being used by a different number of people, all with a different Worksheet Menu Bar configuration. Is there a way of counting how many Menu items are still left in the Worksheet Menu Bar?
So, suppose all that is left (after using the program) in the Worksheet Menu Bar are Excel, File, Tools, Help. How can the porgram find out that there are only 4 items in the menu?
I am taking an EXCEL(online) class and in my current assignment i am to create a 'list' for sorting and querying. However i cannot find the 'list' option, under my DATA option on the toolbar. I have tried all my toolbars and shortcuts but I am unable to find it anywhere.
I have made an excel addin named "INDNUMFOR" and want that this should display under "Data" of main menu bar. thus, a new item (sub menu) should appear naed "IndNumFor" under Data main menu.
