why I get a "Can't enter break mode at this time" message when stepping through the following code (using the F8 key) at the 'With NewButton' line?
(This is code from a Walkenbach book, and it runs fine if I don't step through it. I'm using Excel 2007 btw)
i want to do is throw a break in my vba code if the user selects yes through a msgbox vbYesNo prompt and proceed in debug mode. is it possible to code in a break point in this fashion? my desired pseudo
if user selects yes:
set break point (to send to debug mode at that point... i dont mean to end the code with a END statement.)
if user selects no:
proceed program normally
I've created a spreadsheet that has a number of command buttons acting as hyperlinks navigating to other worksheets within the workbook and other files. The buttons only function when in design mode and no matter what I try the document will not open in design mode. For ease of use I wanted to avoid training people on how to go into design mode, anyone aware of a work around?
View 3 Replies View RelatedI am a happy user of Office 2007 and a casual coder. I am familiar with most of the basics of coding and enabling macos. However today, when I attempted to add some code to a workbook, I noticed that Excel would not actually enter design mode. Even though the design mode button will toggle and stay in the "on" mode, I cannot access VB property panel for any controls, even standard contols. Also, I am forced to CTRL-CLICK objects to select them and then am only presented with standard "Format Control" option, not object properties.
One other odd sympton... I can use the macro recorder to add code to a control (a checkbox for example), but when I try to manually add a line of code referencing that object (using the default name), the VB editor does not recognize the object. For example the IDE would not recognize and code a line for the "checked" property of a checkbox control when I check the box while in macro record mode.
These symptoms suggest to me that there is something wrong with the design mode "switch".
This all used to work fine, but it's been a while and many MS updates to my Windows 7/64 machine (now about 4 years old).
I know Excel is not the best tool for formatting sentences, but is there a way to insert a Line-Break based on a condition? (Either using Formula or VBA).
The situation is:A fixed-width cell, is a concatenation of 5 strings. Each of these 5 strings is a small sentence of 3-6 words; individually much smaller than the cell-width.2 of these 5 are fixed (one at start, one at the end). The other 3 are results of their respective "IF" formulas.Hence, the final sentence wraps over 2-3 lines.I need the 3rd & 5th sentence (the whole sentence, all 3-6 words of it) to start in a new line, only if they are going to be wrapped into two lines.
So If the wrapping results in:
String1 String2 Stri
ng3 String4 Stri
ng5
Then change it to:
String1 String2
String3 String4
String5
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies View RelatedHere is a traditional Indian time calculation
(1 day) 24 Hr = 60 N
1 N = 60 V = 24 Minutes
How to formulate this in excel 2003.
If A1= HH (hour), B1=mm (minutes) - Inputs
A2 should show NN : B2 should show VV - Result
And Viceversa.
If A1= NN (hour), B1=VV (minutes) - Inputs
A2 should show hh : B2 should show mm - Result
hh - hours, mm - minutes, NN - Nazhigai, VV - Vinadi - all are numbers
I have a spreadsheet with agents schedules. What I would like to do is to break down the schedules to determine how many hours are worked between different times. This is what I have: C7:C11 is Sunday Start Times D7:D11 is Sunday's End times. The sheet does Sun-Sat. so Saturday ends at Q11. I want to find out how many agents work from 6am-10am, 10am-12pm, 12pm-2pm, 2pm-4pm, 4pm-6pm, 6pm-8pm, etc. I was originally trying to use a sumproduct but had some problems getting it to work. This is what I was trying: sumproduct(--(C33:C40>=(0,0,0)),--(C33:C40
View 9 Replies View RelatedIs there a way to insert a line of code that, once all workbook open code has completed, will count for 5 seconds and then perform another piece of code?
example,
Once my workbook opens - i would like a message box to appear 5 seconds after.
I'm trying to look up information in "pi" by entering a time that you want to look up say 1800 or 935 and have a cell that would enter it as todays date with that time so I can use it as a timestamp in "pi"
View 1 Replies View RelatedAny way to enter in a total amount of time and then subtract it from a time shown. So for example:
I want to enter in a time of 26 hours, 10 minutes and 2 seconds: 72:10:02
I cannot find a format that allows me to enter the hours in excess of 24
Then I want to subtract it from a time of the day which will be entered as AM/PM: 02:40:02 AM
Then I want to show the result as a time of day: 00:30:00 AM
Lastly, I want to also show the amount of days adjustment: -1d, 00:03:00 AM
How this can be done through cell formulas?
I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.
The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.
I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.
basically I am trying to make a time calculator for work, I want it to automatically add the hours up but then also minus break times and then give me a whole paid total hours worked that week.
CALCULATOR.xlsx
if someone called me at 4:55pm and ended the call at 5:10pm, the whole call lasted 15 minutes. So, I want to show on excel that the call lasted 5 minutes in the 16th hour of the day (4:55pm) and 10 minutes in the 17th hour of the day (5:10pm), for a total of 15 minutes.
View 3 Replies View RelatedI would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.
The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00
Task parameters
A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)
the next line should be filled in automaitically according to the hours needed and the previous end date & time
A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)
and so on for every new line down.
I have a timesheet where user updates start and end time for various tasks.
I have placed a time capture button in the excel sheet (which is simply a macro saying =now() function)
The user clicks it before starting and after finishing the task. The start and end times are captured in adjacent cells.
If the user starts the work, and goes on a lunch break say for 20 min, comes back finishes the task and captures end time, the time difference will not consider break time which is non productive.
How can I incorporate something like 'pause' option so that before he goes for lunch he can temporarily pause the time.
I have a textbox (on a userform) that populates into a spreadsheet. the way I have it set up the user enters a time, but omits the semicolon, and a macro adjusts the time to show the semi colon in the spreadsheet, that is working great. But what I would like to do is to use a textbox change event to force the user to enter a valid time using a 24 hour clock. I have the textbox set to accept numbers from 0 to 2400, using the textbox change event but this allows the user to enter an invalid time such as "1575" basically anthing above 59 for the last 2 digits would be invalid.
View 9 Replies View RelatedI have this code and that allows me to enter the time without using the colon but only in 2 columns, I tried to add to the range "BJ2:BJ105" but this throws an error when I try to enter the time, is there a way to use this code and set it for multiple columns?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("AZ2:AZ105", "BE2:BE105")) Is Nothing Then
[Code] ..........
How to set a column up to show it as the time of day but unless I enter PM after I put the time in the column it always comes up as AM.
Is there some way to just enter 9:09 in column d and or f and have it show up as pm? When I enter 8:10 in column b it automatically adds the AM to it but if I do the same in the column for the night and I do not type in pm it will automatically enter it as am.
I have attached the workbook and did delete the macro - which was just to clear all of my entries but when I click to open it again I am still getting the message about the macro.
In the workbook when I enter the time in column B I can just type in 7:14 and it will automatically add the AM to it. However, when I get to column D or F if I just type in 9:09 or whatever time it is in the evening it will automatically add AM to it unless I type 9:09 pm.
Is there some way to set it up so that I can just enter the time and it will automatically enter pm for everything in that column?
2013 sugar logs.xls
I am trying to enter a blank row everytime the 2ND character of a field changes...
sample cells (ALWAYS COLUMN B)(last row needs to be calculated as it changes daily):
2T2W3D3L4H4N4N4N6C6C
when done:
2T2W3D3L4H4N4N4N6C6C
was trying this:
Code:
Dim chkConfirmRw, LastNameRow As Integer
For chkConfirmRw = LastNameRow To 1 Step -1
'Compare the current cell to the one below it
'If they don't match, insert a row row below the current Row
If Range("B" & chkConfirmRw) Range("B" & chkConfirmRw + 1) Then
Range("B" & chkConfirmRw + 1).EntireRow.Insert Shift:=xlDown
End If
'Decrement the counter and do it again
Next
but it does not work.
I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?
View 9 Replies View RelatedI have a spreadsheet in which I want to track the date and time someone made an entry in an adjacent cell (so if someone enters something in B2, I want the date/time of that cell edit to appear in C2).
When the date/time has appeared, I want it to remain the same and not update when the worksheet is opened the next time. So I can't use NOW to pull it in, because that's going to change every time the file is opened.
in my spread sheet i am trying to use the if function to compare the current time ( vb code used) to a time i enter to alter another cell.
for example
if A1(current updating time) = A2 ( time i choose) then
B1( some value ) = B2( another value)
the point of this equation is to have B2 update its value from B1 every day so today B2= 3 and B1 =4 then tomorrow B2 would = 4 etc , i just dont know how to do it.
I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.
I have so far
A B
1 11:00 7:30
=24*(B1-A1)
Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.
I work at a facility where we care for adults with disabilities and we need to record times that clients arrive and leave. I created an attendance check-in sheet that needs the current time entered quickly. Is there code that will allow staff to double click on a cell and have the current time entered and rounded to the nearest quarter hour?
View 1 Replies View RelatedIs there a way to have a data validation drop down menu but at the same time allowing users to enter free text as well.
View 3 Replies View RelatedThe code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
I want to enter any thing in column (A) and then the date & time automaticaly put in column (B)
enter any thing in (A1), the date & time entered in (B1) automaticaly
enter any thing in (A2), the date & time entered in (B2) automaticaly
and so on ...
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