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
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 put together about 10 separate macros that will log you into a site using the values in a given cell. I was having trouble with the last one because of AutoComplete remembering the username. So I put in an IF statement, and when I used F8 to go line for line, it works perfectly. However, when I click F5 and just let it play through, it doesn't log in. I tried adding a 5 second delay, but that didn't seem to work. I still get a run-time error when I hit play.
It get hung on this line:
Code:
If doc.getElementById("user_name").Value = cUsername Then
But the complete code is this:
Code:
Sub StreetLinks_Login()
'On Error Resume Next
Dim cURL As String
cURL = Worksheets("Sheet1").Range("B9").Value
[Code] ..........
I am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too
sub open file (FileName as string)
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim RowNo As Integer
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName)
Workbooks.Open FileName:=SourceFolder + "" + FileName
' Columns("F:G").Select
' Selection.Delete Shift:=xlToLeft
Save_in_WDrive (FileName)
Set xlx = Nothing
end sub
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'm using Excel 2010. I have a workbook with only 1 worksheet in it and it will be sent to several people. I want that excel file to open in Full View when the end user opens it. Is this possible without any VBA codes?
View 2 Replies View RelatedI have a userform where I want the user to enter the date (US Version)
I want them to enter 121311
I want the textbox which I am using to show 12/13/11 when they exit from it.
Here is my code i am using:
Private Sub mm_built_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim mm_date As Date
Me.mm_built.Text = Format(mm_date, "mmm/dd/yy")
End Sub
What shows when they exit the entry for the above is Dec/30/99
How do I get it to show correctly?
i have some code that allows my users to enter their user ID on double clicking. The worksheet code below shows how on double clicking into Colum 3 (If Target.Column = 3 Then) then the user ID will appear. Q: How do i get the code to work in Columns 3, 5 and 7 but not those inbetween?! I tried using "or" but it allowed the ID to be entered into all columns! i.e. 3,4,5,6 and 7 which is not what I require. In short I need the ID to appear on double clik into every other column.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim user As String
'This refers to the function ReturnUserName, which takes the name from the NT environment
If Target.Column = 3 Then
Target = ReturnUserName
Cancel = True
End If
End Sub
i need an inputbox to appear asking the user to enter an amount(we'll call XX).
Now the code will find that value in Column F, and delete the row that contains the value XX in F
I'm trying to force a user to enter a numeric value, (one numeric value between 0 and 8, inclusive) into a range of cells, prior to exiting the spreadsheet.
The range would be D7:D252, and a single value would have to be entered into each range prior to exiting the spreadsheet.
I am trying to create an input box where the user can only enter a whole number between 1 and 99. i can create the input box but am having trouble specifiying it can only be a whole number between 1 and 99.
I've created a calendar user form, much like many of the examples I seen posted here. I've tried all that I could find but none would allow the user to use the Enter Key to make the selection like the mouse click.
I would like the use of the Enter Key to produce the same results as a Mouse Click.
What I am after is a user form that pops up when I enter ‘Y’ in cells in Column ‘L’ which displays the contents of the cells in that row e.g. if I enter Y in cell L2 a user form would pop up displaying the contents of cells A2 to M2
The A2 to M2 column headers are:-
First Name
Surname
Code
Address line 1
Address line 2
Address line 3
Address line 4
Address line 5
Post code
Telephone
Comments
The user form can be Read only i.e. I do not need to edit the cells (although that would be good) as it will be used as a source document to input into another application, however, a ‘Print’ button to print the form would be beneficial. The only other button required would be a ‘Close Form’ option.
I have user form which allow user to entry their inputs in required boxes (Like TxtFirstName, TxtLastName etc.)
My form is working but which I want is that users should first fill up txtFirstName Text Box. Otherwise we will not able to do edit his/ her name in the last Name Text Box (TxtLastName).
Is there any way to do that?
I have 4 textboxes that the user enters data. I then have a button on the user form.
i would like to write code so that when the user hits the button...
Textbox1 data goes into active cell
Textbox2 data into the cell below Active cell
Textbox3 below it
and textbox4 below it
then close the user form.
Is it possible to lock a cell so that the user must enter the number manually as opposed to using a formula.
There is a cell on one of our spreadsheets that the user should be hand entering the numbers from the general ledger this is done to ensure that the general ledger balance is the same as the spreadsheets totals. However most of the user insert a formula that just copies the number from above into this cell that should be hand entered.
how can I load a text box where the user can push enter and go to another line? That would be like a comment box. If I put the traditional text box, I can only write in the first line and enter doesn't work. Is there a way to program it, where it would work?
View 2 Replies View RelatedI need to force users to enter one of these words only (Year End) or (Qrt) using inputbox method.
Note that user must not leave the inputbox until he/she enter one of these words.
Say a user inputs information into the user form, when they click enter, it enters all the data on the next available line in a workbook.
Is is possible for the user to click a cell on a previously entered row, and have the userform populate with the existing information?
example.
say the user has to fill out 3 separate areas. 1, 2, 3. However the user only has data for 1 and 2. They go ahead and enter the infromation for 1 and 2 and click submit to transfer to sheet. Now later he gets information for area 3, can the row the information that he previously entered, lets say column 1, row 1, and it reopens the userform with the information for 1 and 2 prefilled from what he entered previously?
I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.
View 9 Replies View RelatedHow to set up a Inputbox to make the user pick the start line and end line for a loop
the sheet will have items on it numbered 1,2,3,etc I want a process done to the lines the user selects by item number not cell range.
line 4 to line 20 for example.
I've been going round in circles with this for hours and have got no-where. I 'm trying to get Excel to paste in a set pattern of data based on an input cell.
In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on
The user would enter the data in column A, starting in row 2
If the date in A2 = May 12 then Excel would enter 10 in cell C2, 20 in cell D2 and 30 in cell E2. If the date in A2 is changed to June 12, then the previous data would be cleared and Excel would enter 10 in E2, 20 in F2 and 30 in G2.
Date would be entered in A3, A4, A5 etc and I would want it to post the same fixed data depending on the date the user enters in teh relevant row.
I've tried IF statements and also some code but can't even get near it working.
I'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
Basically, in the "Thisworkbook" code , i have some code in the Workbook_BeforeClose section. Currently , it autosaves the workbook in a folder i have specified.
However, i need to add some code.I want to check that a certain cell has a value in it before the user closes the workbook, and if the cell is empty, show a messagebox asking him to enter a value.
I know how to get a messagebox to pop up, the only thing is once the user clicks the OK button,
i need the rest of the code execution to pause, allowing him to make the change then if he clicks the "X" (top right of the screen) to close the file or application, the filesave dialog appears and he can then save the document.
how to go about this because at the moment when user clicks ok, the messagebox just disappears and filesave dialog appears and he doesn't have a chance to edit the cell.
I have produced a basic search/lookup facility on an Excel workbook that simply allows the user to enter a post code and some details will be returned using a vlookup. The document is going to be rolled out to a number of operational users so I want to basically 'lock down' everything I can in the document (basically everything except the data entry cell) and make it fool proof- I have locked all cells apart from the data entry cell and have made the file read only.
The only issue I am encountering is when the cursor is in the one 'unlocked' cell (i.e. the one the users will enter the post code into); it appears that you can break the document. For example, when the cursor is in the 'unlocked' cell, I am able to go to Tools > Options and change various settings including cosmetic colour changes but also cell calculation which breaks the lookup functionality. This is probably enabled as the cell is unlocked, but if I lock the cell, when I protect the document, it disables data entry!
if there is another way of providing this one cell for users to input data into for the vlookup to work whilst locking down the rest of the document to ensure that no-one can break it?
I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..
If Range("o8").Value = "Agency" And Range("p8").Value = "" Then
MsgBox "Please provide name of agency in cell p8"
Sheet9.Shapes("cross").Visible = True
Else
I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.
[Code] .....
Attached File : Staffing Report 1.90.xlsm‎
Is there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:
Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub
The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.
