I have created a pop up calendar using VBA with the help of VBA Tips at www.fontstuff.com/vba and it works very well. However it has the default date of 12 March, In the tutorial that i used there was some code to use todays date if the cell (in which i was going to put a date) is empty. Which is as follows
Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub
yet when the calendar does pop up, the date remains at 12 March (the calendar in the visual basic editor reflects the date correctly). I've tried to change numerous amounts of properties, without success
I have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.
Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.
If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer Insert More Controls Calendar Control 12.0
I am using a great pop up calendar without ActiveX made by a guy called Phil Johnson. When you click a specified cell the calendar appears. The only problem is that the calendar doesn't default to today's date when it opens.
I have the original file Calendar source code if anyone needs it.
I currently have a worksheet that when a user clicks on the cell, a calendar form opens. From here they can select a date, click ok, and the date gets placed in the corresponding cell they clicked on.
Now if the user needs to change that date, i want them to be able to click on the cell again, and when the calendar opens, the date in the cell would be selected on the calendar. I was able to do this in a userform, but im not sure how to edit it to work on the worksheet. I am using the below to populate the calendar.
Code: private sub worksheet_selectionchange(byval target as range) if not intersect(target,range("aa16:aa24")) is nothing then .show calendar When the user clicks the date, the below code places it in the cell.
Code: private sub ok_click() with active cell .value=calendar1.value end with unload me end sub
Now the below code was used when I was using a userform, but im not sure how to edit this to pull the date from the active cell.
Code: privatesub userform_activate() me.calendar1=date if not tb is nothing then if isdate(tb.value)then me.calendar1.value = tb.value end if end sub
I have some VBA code that crates a PDF saves it in a direcory gained from Cells in Sheet and i now wanto to email that saved PDF but it keeps coming up with file path not recognised or found.
Code: Sub saveandclose2() Dim MyPath As String Application.DisplayAlerts = False
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.
I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?
I originally posted this in the "Excel New Users forum" - i guess that was an error, but I'm very new (second post) and very new to VBA in general - so please be gentle!!
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
I am trying to create a file which will contain several thousand links to files which will not be created for some time. It's purpose is to trend KPI's and budget compliance over time. Is there a way to create these links in Excel 2007 without having to cancel the annoying "Update Valeus" window? Here is an example of that is looking for a file that will be created in 2 weeks.
=IF(AND(ISNUMBER(MATCH(B12,'V:MaintenanceWork ManagementPublicWork Management KPIsSAP SchedulingJobs With Operations In The Past[Jobs with Operations In The Past 11-4-09.xlsx]Plant Section Sort'!$B:$B,0)),(ISNUMBER(MATCH(I12,'V:MaintenanceWork ManagementPublicWork Management KPIsSAP SchedulingJobs With Operations In The Past[Jobs with Operations In The Past 11-4-09.xlsx]Plant Section Sort'!$I:$I,0)))),"Duplicate","New")
I've tried declaring my object variable (TabOrder) as module-level within a Sheet Module and also as local within a Subroutine in the Sheet Module. In both cases, when the debugger steps through the code, the object is destroyed almost as soon as it is created.
I'm attempting to enter a formula into cells on a spreadsheet through a macro.
I want the formula to read:
=if(isblank(G1),"",month(G1))
but because this is being created within a macro it doesn't like the double quotations.
I can get it to display "0" but i want those cells to be NULL. Right now my only idea is to make an IF statement in the code that doesn't insert a formula in those instances, but I'd like to keep them all as formulas for later changes. Anyway around this dilemma?
Example Code (only a piece):
Code:
With Range("F1") .Value = "=if(isblank(G1),,month(G1))" .AutoFill Destination:=Range("F1:F" & lastrow) End With
I'm working on a Tool, where every change should be saved in a new Version. So if the user changes any number, he should click my selfcreated save-button, which takes todays date in the filename (no problem so far).
I tried to disable the "normal" save-button with:
Code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox ("saving cancelled!") End Sub
Unfortunately this code won't allow my own button to save too. Is there any other way? Maybe sth like if "normal" saving is used, then automatically activate a makro (which is also on my button) instead of just saving?
I'm a novice at Excel and need some help. We have a local area Dollars for Scholars high school chapter auction coming up and I need to sort the information that has been entered into an Excel spreadsheet (there are several hundred contacts). I need this sorted to create labels to send out donation letters. Last year we used ACT, but the chapter board wants to use Excel so everyone can have the info at their fingertips (since everyone doesn't have ACT). The information was entered in column A like the following (in rows not columns):
Name Address 1 Address 2 City, State telephone #
Name Address 1 Address 2 City, State telephone #............
I've created and excel spreadsheet that has code like this in a Module named Print w/ in the workbook: Sub Print_wsPrint() Sheets("Print").PrintOut End Sub
I have saved it as an MS Office Excel add-in.
I added the add-in to my excel environment via Tools, Add ins...and let it copy it to my C:Documents and SettingsjohnApplication DataMicrosoftAddIns directory.
But yet when I create a menu button and assign the Print_wsPrint to a custom menu, it complains and says "The macro Model.xls!Print_wsPrint cannot be found," where Model.xls is the workbook I am in. It's in my add-in, not in the current wb.
How do I get a random name from a list that I have created? The random feature only deals with numbers. I want to be able to push F9 or click on something and have a cell randomly pull a name from the list.
I've created an add-in that runs a simple macro. The problem I have is that when I run the add-in it opens the workbook I originally created it from - is this what I should expect it to do? How do I stop this from happening?
Also, do I need to have the workbook from which I created the add in located in the same place on every machine I want to use the add in on? At the moment if I move the original workbook the add-in won't work.
How to put a formula into my userform created in Excel.
What I have is 4 Combobox's which can select either 0,1,3 or 9 then each box has a weight which that number must be timed by this is the excel formula:
=SUM(L48*10,M48*10,N48*8,O48*6) so that was 9x10,3x10,9x8,3x6 Which gives me a sum of 210.
Can this be added to the userform so when the user selects the number from the dropdownbox it will calculate it into the total score?
This is a screen shot of the userform : Capture.JPG
I have created a toolbar with buttons that run macros. When the macro is assigned to that button the macro will run fine. However, when the file name of the excel workbook has changed (file relocated or name changed) then the macro (button in the toolbar) does not work and has to re-assigned in the toolbar. Is there anyway of getting around the problem of having to reassign the macro to the toolbar button whenever the file name/directory changes.
I have a button that will create tabs based off of information from a text box and now I want to select one sheet and insert text into certain cells so when they create a new tab information that is generally going to be there is autogenerated. I have an example of my code below. Please point me in the right direction cause I cant find anything really helpful that I havent tried. Everything is under the "gateway" then "New tabs" and PBG-Activity list is the only one with this macro.
I have the following procedure that creates sub folders for items in a named range (Room) in the parent folder (Pictures). The procedure works as expected; a sub folder is created for each item in the named range. What I'd like to do is give the User feedback as to what sub folders were created. This is the code I'm using:
I have the following code that creates a new workbook and names it after the value of a cell in another workbook. Another part of the macro will switch back and forth between the original workbook and the newly created workbook.
What is the best way to refer to new workbook? I'm familiar with the "workbooks "VARIABLE NAME".Activate" style but, the name of the new workbook will change every time the macro runs and I'm not sure how to refer to it.
the code below.
HTML Code: Sub MakeNewBook() Dim wB As Workbook Dim nPath As String
I have created a spreadsheet that I wish to save as a template. However, when the a new workbook is created from the template it needs to be saved as a Macro Enabled workbook not just a workbook which is the default. To achieve this I added the line 'thisworkbook.FileFormat=xlOpenXMLWorkbookMacroEnabled' to the 'beforesave' macro for the workbook. This works fine except that it makes saving the file as a template quite difficult.
How I can trap the first time a workbook created from a template using New is saved would be most useful.
I would like to name a shape in excel upon its creation ...... I Can name the"line" in the macro....But I would like every other line created after its Name to be"numerated or given a unique identifier while keeping its parentname "Jimmy". I believe it has to do with a "eventHandler " ! code attached below....... Like"Jimmy1" "Jimmy2" etc
I am having a excel vba code that copies tab from different excel file and then i want to use in sql query. It is always giving me error saying that "The Microsoft Access database engine could not find the object 'rahul'. Make sure the object exists and that you spell its name and the path name correctly. If 'rahul' is not a local object, check your network connection or contact the server administrator."
Though, when i rerun the macro after reopening the excel file, it is working fine. Seems like tabs dynamically created cant be used. Is their any way to get it done at runtime? I want to delete new created tab once data from new tab is inserted to required tab.
my macro calls first copyAllSheets(filename) and then writeSheet().
Code:
Private Function copyAllSheets(ByVal fname) Dim destSheetCount As Integer Const FUNCNAME = "copyAllSheets" On Error GoTo ErrorHandler Set wb = Workbooks.Open(fname) destSheetCount = ThisWorkbook.Worksheets.Count + 1 wb.Worksheets("Buyer").Copy after:=ThisWorkbook.Sheets(1) ThisWorkbook.Worksheets(destSheetCount).Name = "impBuyer"
[code]........
This is part of code so probably it wont be easy to understand the flow. but mainly i want to know how can i have newly created tab as table_name.
