I see that only letters are allowed for macro shortcuts. And I have only been able to set shortcuts starting with "Ctrl" or "Ctrl+Shift". I want to know how more shortcuts can be set like shortcuts beginning with "Alt" or "Alt+Shift".
Also, how can macro shortcut created in one worksheet be made available to any worksheet without using the XLStart folder method?
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
Macro2 is in ThisWorkbook.
Code:
Application.MacroOptions Macro:="Macro2", Description:="", ShortcutKey:= _
"d"
How to do the above when this workbook is not active workbook.
Anyone know if you can place a shortcut on the desktop using VBA code?
View 9 Replies View RelatedIn column A, I have many entries such as AA-1, AA-2, BB-1, BB-2, AA-3, etc.
How do you count the number of entries for only those beginning with "AA" or beginning with "BB"?
I have a routine that by clicking one button, that calls a macro, that currently opens Excel, or Word, or WordPerfect. The following macro uses a Case Statement looking at what the extension is, such as for Excel . . . xls
I have added a case statement for a shortcut . . . exe
I've created a calendar in VB following the instructions in this link - [url]
The calendar works fine but the problem I've got is I've got unwanted copies of the shortcut when I right-click. If you scroll down the URL, right at the bottom in blue is a paragraph that describes the problem and how to get rid of it. I've tried this numerous times but the duplicate entries keep re-appearing.
I've attached my workbook in the hope that any of you experts would be so kind as to take a look and offer any help or advice on how to fix this problem?
Is there a way to find and edit all the shortcuts I created in one (and in more then one) Excel spreadsheet?
View 2 Replies View RelatedSome expert users can unhide a sheet in a worksheet which is protected by password. But I don't want to place a button in the worksheet for everyone to see and wonder what it's for. I can record a macro and assign a keyboard shortcut but I can't do it it with existing macros ?
View 3 Replies View RelatedShortcuts for moving tabs to different files. A copy of the tab, I mean.
View 1 Replies View RelatedHow can I create keyboard shortcuts in the easiest way.
E.g.: I would like to create my own shortcut for "Merge".
unknowingly i must have deleted some shortcuts via VBA code.
now, the shortcut key (excel 2003) Alt+Insert+Row works!
But, Alt+Insert+Column does not! this works in my colleagues computers which also have Excel 2010.
is there a way to reset all keyboard shortcuts on my computer? i have tried in vain to find a solution but not found one option.
I currently have a navigation menu with 9 options however these options are based on Range names that I have set up.
The concern is that if someone deletes the cell that contains the range name, obviously that option in the navigation won't work, nor would you want it to.
However if someone re-enters a cell that you would want linked up with the navigation there's a lot more action involved.
What i'm looking for is a macro that can be used through a button, Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.) and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")
I have a Start Shift and an End Shift time,
Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs
I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.
I am being passed a series of positive and negative numbers in a column - the only problem is that the negative numbers have the - at the end of the series i.e.1234-.Is there someway I can change this with a macro so they read -1234.
View 3 Replies View RelatedI need to put in a list of telephone numbers
i.e 01 is the std and 12345 is the number.
I need it to look like 0112345 but excel automatically removes the zero at the start of the number.
I have the following macro........
Dim i
If ActiveSheet.Index = Sheets.Count Then Exit Sub
For i = ActiveSheet.Index + 1 To Sheets.Count
If Sheets(i).Visible = xlSheetVisible Then
Sheets(i).Select
Exit For
End If
Next
This macro starts at which ever sheet I am on and goes from front to end say 1 to 16.....my question is how do reverse the code to go to the next unhidden sheet from 16 to 1 ?
I have a column with data and I want to add, in each row, the following:
at the beginning of row:{color=blue}
and right at the end of the row: {/color}
so that
excel
becomes
{color=blue}excel{/color}
I tried to do this with the formula:
"{color=blue}"&A1&"{/color}"
which yields nothing.
I've created Keyboard Shortcuts to run Macros in "Workbook A". Problem is, if I have "Workbook B" open in the same Excel instance/process as "Workbook A" the Keyboard Shortcuts will run the Macros in "Workbook A".
Is there a way to restrict the Keyboard Shortcuts to only run the Macro if Workbook A is active?
Or is there a way to code the Macro to run its procedures only if Workbook A is active?
I want to save phone no as +99 9876543210 in excel 2003 on my xp pro machine. But if i give a + sign in the cell, some blue dotted rectangle shows up and everything messes up.. I think it is treating it as a formula or something... how can i save this in the cell. tell me in detail if you are going to tell me about macros or vb code as I don't know how to insert code or program macros.
View 3 Replies View RelatedI have some code that adds worksheets to a workbook and names them appropriately, I now need to change that to add the worksheets at the beginning of the workbook and am struggling with the syntax.
My first sheet is sheet1.
I need to change this piece of code
Sheets.Add After:=Sheets(Sheets.Count)
I have a column which contains a series of alphanumeric characters some start with the alpha characters and others end with them.
As an example ABS1234, 1234ABS, 12k34AB needs to be ABS1234, ABS1234, AB12k34. The number of alpha characters at the end will be a minimum of 2 and a maximum of 3. Is there some way that I can move the Alpha characters, at the end, so that they are always at the beginning of the string?
I have lost the formula for this looked everywhere, I have Content that looks like this e.g 1dg, 15gh, 8fj, 20lk,
I reqiure the formula to split the number into another cell(numbers will be no more than 2 digits), I already have the formula for the letters.
I want to add blank spaces to a cell with text
My cells have up to 14 spaces
If a cell as 1 or 2 characters -I want to add 6 spaces in front of text.
If a cell as 3 or 4 characters-I want to add 5 spaces in front of text.
If a cell as 5 or 6 characters-I want to add 4 spaces in front of text.
If a cell as 7 or 8 characters-I want to add 3 spaces in front of text.
If a cell as 9 or 10 characters-I want to add 2 spaces in front of text.
If a cell as 11 or 12 characters-I want to add 1 spaces in front of text.
I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
[Code] .....
I am trying to create a list of customer credits for my store. I have the person's name, the date of their credit, and the amount of the credit. This list will eventually be substantial and I would like to know how I can set it up so that when I open the worksheet I can enter new names from the top of the list and have the rest of the list shift downwards. This would stop me from having to scroll to the very bottom to add new info every time.
I would like to have it set up this way so that eventually I could put a search query at the top of the page (i'm thinking of rigging something up using vlookup) so that when my employees want to access the data base they can easily search a name and have the relevant info brought up, or can easily type in new store credits.
I have a simple code that should insert the text at the beginning of the text file (the text file already has some text in it). When i use the following code, it just appends the line to the end of the text. I need it to append it to the start of the text file.
Code:
Private Sub CommandButton1_Click()
Dim file As String
lastrow = Range("D65536").End(xlUp).Row - 4
file = "C:Users11126923Desktop est.txt"
Open file For Append As #1
f = "hi test3"
Print #1, f
Close
End Sub
In VBA, does anyone know how I can find the beginning and end of a named range?
eg: Range "CellsToTotal" is A1:A10, if I know only the name of the range, can I retrieve both A1 and A10 values?
What I have done is named a range on the fly as so (where vStartCellCoordinates and vEndCellCoordinates are both variables holding 2 ints):
ActiveWorkbook.Names.Add Name:="CellsToTotal", RefersTo:="=" & Cells(vStartCellCoordinates(1), vEndCellCoordinates(2)).Address
What i'd like to do is then set a third arbitrary cell to sum up the range as a formula (note the following is a wierd adapted pseudocode thing):
Cells(iRow, iCol).Value = "=sum(" & start(Range("CellsToTotal").Address(0, 0) & ":" & end(Range("CellsToTotal").Address(0, 0) & ")"
How can I make this work?
Is there a snatch of macro that can assign two variables with the date for the beginning and ending date of the current week?
I need
A= 4-19-2009 12:00AM
and
B=4-25-2009 11:59 PM
Based on the current week being week 16, with the variables changing as the weeks progress.