Calendar Initialize

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Change text in a cell from a command button on a Userform I am getting a runtime error 1004 I do realise that I am not addressing the cell correctly but cannot find out how do do this. I have 1 Sheet, 1 Userform and 1 module. My code (below) explains what I want to do. Any code to overcome this error would be greatly appreciated. Private Sub CommandButton2_Click() Sheets("sheet1").Range("K1").Text = "Off" End Sub -- Derek Tag: Calendar Initialize Tag: 1004957
  - 2
    - Formating date ranges for weekends I have a row of dates which start from =TODAY() and increment by 1 along the row giving a sequential row of dates. The dates are all highlighted in one color I would like to highlight weekends in a different color format to make them stand out. I would like this to be done automatically. Is there anyway to do this? Thanks, Roger Tag: Calendar Initialize Tag: 1004955
  - 3
    - Macro Excel to automatise the copy of data from one worksheet to another sheet Hi, I am looking for aid with the following macro: - excel file with 5 worksheets - 4 worksheets contain data which has to be automatically copied to the 5th worksheet each time you run the macro, or each time you press some button I tried to record a macro this way: - go to sheet 1, put mouse on first cel, push control+shift+end - then i do control+c - i go to sheet 5 (the "total" sheet) and push control+v --> the problem: how can i make excel understand he has to put the mouse in the cell after the last cell of the data which i just copied? --> other problem: does control+shift+end depend on the amount of rows i copy? i do not want a certain of rows to be copied. the sheets are updated by someone else, so i don't know how many rows he added or deleted. excel can therefore not always copy the exact amount of rows i copied recording the macro.... Please help me out if someone knows how to do this... Thanks in advance for any suggestions, advice.. Kristien Tag: Calendar Initialize Tag: 1004954
  - 4
    - Old VBA code modification help Hello, I have the following existing code that find the first eligible row, and to start filling in data from an array, looking for the next eligible row (skip the row with "ASC" written in column I) after filling in 8 columns (I:N) and looping as many times as is specified in the call from the main routine. I would like to modify the code so that instead of skip the entire row if column I is "ASC", but only skip the cell with "ASC" instead, but still in fill the data without "ASC" on the same row. Old code sample data: A H I J K L M N O P Line# Mod 1300 1300 1500 1500 1700 1700 1100 1100 001 300 ASC 002 100 x x x x x x x 003 200 x x x x x x x x skip row 2 with line# 001 cause "ASC" is in column I2 so skipping cell J2 thru N2). Where x is data that the macro fill in. New code wanted: A H I J K L M N O P Line# Mod 1300 1300 1500 1500 1700 1700 1100 1100 001 300 ASC x ASC x x x x ASC 002 100 x ASC x x x x x ASC 003 200 x x x x x x x x it does not skip row 2 with line# 001, but rather fill in the missing cell (J2, L2, M2 and N2) without "ASC" in the cell. Here is my old VBA code: Const OP = 0 Const SO = 1 Const DD = 2 'delivery date Const Ref1300 = 0 Const Ref1500 = 1 Const Ref1700 = 2 Const Ref1100 = 3 Private Sub Macro() ' Macro Macro ' Dim R1300M100(10000, 3) Dim R1300M200(10000, 3) Dim R1300M300(10000, 3) Dim R1500M100(10000, 3) Dim R1500M200(10000, 3) Dim R1500M300(10000, 3) Dim R1700M100(10000, 3) Dim R1700M200(10000, 3) Dim R1700M300(10000, 3) Dim R1100M100(10000, 3) Dim R1100M200(10000, 3) Dim R1100M300(10000, 3) With Sheets("100") LastRowSh1 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh1 <> 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh1, "I")) For Each Cell In ColIRange If (Cell <> "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("200") LastRowSh2 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh2 <> 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh2, "I")) For Each Cell In ColIRange If (Cell <> "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With With Sheets("300") LastRowSh3 = _ .Cells(Rows.Count, "I").End(xlUp).Row If LastRowSh3 <> 1 Then Set ColIRange = .Range(.Cells(2, "I"), _ .Cells(LastRowSh3, "I")) For Each Cell In ColIRange If (Cell <> "ASC") And _ (.Rows(Cell.Row).Hidden = False) Then .Range("H" & Cell.Row & ":X" & Cell.Row).ClearContents End If Next Cell End If End With LastRowSh4 = Sheets("Data"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1300M300Count = 0 R1500M100Count = 0 R1500M200Count = 0 R1500M300Count = 0 R1700M100Count = 0 R1700M200Count = 0 R1700M300Count = 0 R1100M100Count = 0 R1100M200Count = 0 R1100M300Count = 0 With Sheets("Data") For Sh4RowCount = 3 To LastRowSh4 If IsError(.Cells(Sh4RowCount, "L").Value) Then OPeration = -1 Else OPeration = .Cells(Sh4RowCount, "L").Value End If If IsError(.Cells(Sh4RowCount, "A").Value) Then Order = -1 Else Order = .Cells(Sh4RowCount, "A").Value End If If IsError(.Cells(Sh4RowCount, "P").Value) Then Model = -1 Else Model = .Cells(Sh4RowCount, "P").Value End If If IsError(.Cells(Sh4RowCount, "H").Value) Then DDate = DateValue("1/1/1300") Else DDate = .Cells(Sh4RowCount, "H").Value End If If IsError(.Cells(Sh4RowCount, "O").Value) Then Item = "" Else Item = Trim(.Cells(Sh4RowCount, "O")) End If If Left(Item, 2) = "13" Then If Model = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ Order R1300M100(R1300M100Count, DD) = _ DDate End If If Model = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ Order R1300M200(R1300M200Count, DD) = _ DDate End If If Model = 300 Then R1300M300Count = R1300M300Count + 1 R1300M300(R1300M300Count, OP) = _ OPeration R1300M300(R1300M300Count, SO) = _ Order R1300M300(R1300M300Count, DD) = _ DDate End If End If If Left(Item, 2) = "15" Then If Model = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ Order R1500M100(R1500M100Count, DD) = _ DDate End If If Model = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ Order R1500M200(R1500M200Count, DD) = _ DDate End If If Model = 300 Then R1500M300Count = R1500M300Count + 1 R1500M300(R1500M300Count, OP) = _ OPeration R1500M300(R1500M300Count, SO) = _ Order R1500M300(R1500M300Count, DD) = _ DDate End If End If If Left(Item, 2) = "17" Then If Model = 100 Then R1700M100Count = R1700M100Count + 1 R1700M100(R1100M100Count, OP) = _ OPeration R1700M100(R1700M100Count, SO) = _ Order R1700M100(R1700M100Count, DD) = _ DDate End If If Model = 200 Then R1700M200Count = R1700M200Count + 1 R1700M200(R1700M200Count, OP) = _ OPeration R1700M200(R1700M200Count, SO) = _ Order R1700M200(R1700M200Count, DD) = _ DDate End If If Model = 300 Then R1700M300Count = R1700M300Count + 1 R1700M300(R1700M300Count, OP) = _ OPeration R1700M300(R1700M300Count, SO) = _ Order R1700M300(R1700M300Count, DD) = _ DDate End If End If If Left(Item, 2) = "11" Then If Model = 100 Then R1100M100Count = R1100M100Count + 1 R1100M100(R1100M100Count, OP) = _ OPeration R1100M100(R1100M100Count, SO) = _ Order R1100M100(R1100M100Count, DD) = _ DDate End If If Model = 200 Then R1100M200Count = R1100M200Count + 1 R1100M200(R1100M200Count, OP) = _ OPeration R1100M200(R1100M200Count, SO) = _ Order R1100M200(R1100M200Count, DD) = _ DDate End If If Model = 300 Then R1100M300Count = R1100M300Count + 1 R1100M300(R1100M300Count, OP) = _ OPeration R1100M300(R1100M300Count, SO) = _ Order R1100M300(R1100M300Count, DD) = _ DDate End If End If Next Sh4RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1300M300, R1300M300Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call SortData(R1500M300, R1500M300Count) Call SortData(R1700M100, R1700M100Count) Call SortData(R1700M200, R1700M200Count) Call SortData(R1700M300, R1700M300Count) Call SortData(R1100M100, R1100M100Count) Call SortData(R1100M200, R1100M200Count) Call SortData(R1100M300, R1100M300Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100, "100") Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200, "200") Call InsertData(R1300M300, R1300M300Count, _ Ref1300, 300, "300") Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100, "100") Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200, "200") Call InsertData(R1500M300, R1500M300Count, _ Ref1500, 300, "300") Call InsertData(R1700M100, R1700M100Count, _ Ref1700, 100, "100") Call InsertData(R1700M200, R1700M200Count, _ Ref1700, 200, "200") Call InsertData(R1700M300, R1700M300Count, _ Ref1700, 300, "300") Call InsertData(R1100M100, R1100M100Count, _ Ref1100, 100, "100") Call InsertData(R1100M200, R1100M200Count, _ Ref1100, 200, "200") Call InsertData(R1100M300, R1100M300Count, _ Ref1100, 300, "300") End Sub Sub SortData(ByRef MyArray() As Variant, Count) 'Sort by Delivery Date For i = 0 To (Count - 1) For j = (i + 1) To Count If MyArray(j, DD) < MyArray(i, DD) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp Temp = MyArray(i, DD) MyArray(i, DD) = MyArray(j, DD) MyArray(j, DD) = Temp End If Next j Next i 'Sort by Operation For i = 0 To (Count - 1) For j = (i + 1) To Count If MyArray(j, OP) > MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp Temp = MyArray(i, DD) MyArray(i, DD) = MyArray(j, DD) MyArray(j, DD) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") <> Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "Q"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty(.Cells(RowCount, "I")) And _ (.Cells(RowCount, "H") <> Model)) Or _ (.Cells(RowCount, "I") = "ASC") Or _ (.Rows(RowCount).Hidden = True) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub Tag: Calendar Initialize Tag: 1004952
  - 5
    - Application Level Events I need a way to have a sub within my xla to run every time any workbook gets or losses focus. So if a new workbook is opened or closed, or you just switch back and forth between two workbooks. Tag: Calendar Initialize Tag: 1004945
  - 6
    - highlight alternate row in excel Dear Friends, i would like to have a command or vba-code for highlighting alternate row. e.g. a1..h1, a3...h3, a5...h5, a7...h7 thanks Tag: Calendar Initialize Tag: 1004943
  - 7
    - How to automatically delete formulas in an adjacent column I have two columns (A and B) which return results from formulas in other cells. I would like to have a macro read down the list of column A and if the result returned is empty/zero, the contents formula of its adjacent column B cell be deleted. I have complex code which doesnt work when the column B result returns a blank (which is the case when A is also blank), so this solution, although drastic will solve my problem adequately. Can anyone help? Thanks, Roger Tag: Calendar Initialize Tag: 1004938
  - 8
    - Summing Group and insert value next to it Hi In column A, I have names that appear as ABC 01, ABC 02,ABC 03, I want to sum the group ABC, XYZ or any other groups which appear in the column A and put a value in column B depending on the sum total figure. For example, if the total sum for ABC is more than 100, all the entries in the column B related to ABC, ie ABC 01, ABC 02, ABC 03.....so on, will have a Y in the respective rows, otherwise, N will be input in the related entries. Thanks in advance. regards, xlsops Tag: Calendar Initialize Tag: 1004934
  - 9
    - Using Solver to Minimise the Result of Another Iterative Routine I've written an iterative Trapezoidal integrator in VBA for integrating an analytic function f(x), where f(x) is defined in a cell, for example, B1: =3*A1^3 + 2*A1^2 + A2^3 - A3, where the integrating variable x is in cell A1, and cells A2 and A3 contain parameters. In this example, the cells B1, A1, the cells with the lower and upper limits, and the cell to receive the result, are passed as arguments. All this works fine. Now, this is the part I 'm wanting to do, but cannot achieve. After the integrator produces its result, I'm wanting to call the Solver tool to minimise the integral result (target cell), adjust the parameters in A2 and A3 (changing cells), and then for the integrator to be run again, producing its next result. This process is to be repeated, until such time as the parameters have been so adjusted by the Solver, and the integral result has reached its minimum value. ie, 1. Set up initial values. 2. The integrator determines its result. 3. Solver reads the result, then adjusts the two parameters. 4. Repeat steps 2 and 3, until the Solver has minimised the integral via the two changed parameters. I am unable to determine how to combine the iterative integrator with the Solver in the above manner, so they alternately operate until such time that the integral has been minimised. Any guidance greatly appreciated. Tag: Calendar Initialize Tag: 1004880
  - 10
    - set an array of shapes visibility Hi, Is it possible to use an array to set the visibility: ActiveSheet.Shapes(Array("image1", "image2", "shape3", "shape5")).Visible = True Thanks Tag: Calendar Initialize Tag: 1004877
  - 11
    - VBA croaks on Nodes.Clear if TreeView is *not* Visible. This just cost me days of effort, so I will share it with you: In my Excel 2002 VBA 6.3, you must set TreeView.Visible =3D True if you wish to clear the tree and start over. If you don't, sick things happen. First of all, on immediate startup of a UserForm with a TreeView: Nodes.Clear has no adverse effect. (Maybe because since the tree is empty, it=92s not doing anything). Moreover, these work unconditionally too: - Nodes.Add - Nodes.Remove(i) But there endeth the joy ride. If you wish to wipe the tree's nodes, then, if TreeView.Visible =3D False: Remove(i) seems severely bugged: - It executes but does nothing. It does NOT remove its node. - No error is thrown. You=92re left completely in the dark why Nodes.Count didn=92t change. Clear may or may not work, but it=92s useless anyway. Because if you do it, then: The first .Add thereafter throws unhandled runtime error =93Method Add of object INodes failed=94. (What=92s an INode, some Windows Interface thingy?) *But*, the bottom-line news isn=92t bad. How long does a Clear take, anyway? A fraction of a second? Because on the very next line after .Clear, you can hide the tree again, and start adding new nodes without the user seeing your stupid screen churn. Actually, I misspoke. I have found that, seemingly amazingly, adding nodes does NOT seem to update the tree visually. But removing nodes does. So I hope I can help someone avoid the pain I just went thru. *** By the way, is using TreeViews tantamount to speaking Hittite? Will someone tell me why, when I use Google Groups=92 (to me totally perverse, unworkable interface to) microsoft.public.excel.programming, I see only ONE instance of term =93INodes=94 in a =BC million submissions? = I mean, huh? And--you guessed it--that one mention is mine, from last May. For which I thank Norman Jones for replying. *** Tag: Calendar Initialize Tag: 1004869
  - 12
    - Help in VBA. Dear all experts, I am totally new to VBA. I have make use of a set of code provided by Mr. Paul Sheppard. But I am encountering errors. What I want to do is: To update the 13 worksheet's name referencing to another file called Staff Names for timesheet.xls, worksheet name called "IT" and from cell b2 ~ B14 This Staff Names for timesheet.xls path is C:\Document folder\Timesheet Sub namesheets() Dim arr As Variant arr = Workbooks("Staff Names for timesheet.xls").Worksheet("IT").Range("b2:b14").Value For i = LBound(arr) To UBound(arr) Sheets(i + 1).Activate Sheets(i).Name = arr(i, 1) Next i End Sub This is definitely a simple questions to the experts out there, appreciate very much. Thanks! Tag: Calendar Initialize Tag: 1004845
  - 13
    - VB6 Com Addin versus VBA Addin I have a VBA Addin and a freind suggested that I move it to a VB6 com Addin as it will improve the speed. I am happy that it will improve security but that is not an issue. Please could someone give some insight into this. Thanks in advance. Meldrum Tag: Calendar Initialize Tag: 1004842
  - 14
    - Presentation quality Excel tables? HI all, After collecting many data, I want to summarize data into a table and then put into both powerpoint and Word document for presentation and report use. Could anybody show me how to design a Excel table that really looks great? Any templates? I designed a bunch of tables myself and because I am lacking of artistic senses, my tables look ugly... One requirement is that I have to make this data table in Black/White, because in the report, mostly people print it out in Black/White, the color will get distorted if it document is in color mode. To save overall time, why don't I just design the whole table in Black/White and reuse the tables both in report and presentation? Unless there is a way to easily convert colorful presentation mode table into a Black/ White report mode table? Anyway, I really want to make my data tables visually appealing and a lot of tables are comparing data and results... want to make sure my audience and my boss gasp at my work. But also to save time, I have to start the table in Excel, right? Please help! Thanks a lot! Tag: Calendar Initialize Tag: 1004834
  - 15
    - Use of CallByName() All the references I have found on this procedure appear to be for use in a class module. I would like to use it to call a procedure in Module1. Is this possible? What is the object to be specified in CallByName object,procname,calltype,[args()] Tag: Calendar Initialize Tag: 1004831
  - 16
    - Workbook Open Event Procedure Hi !! :) Could someone tell me if it is possible to work with another workbook from a workbook open event procedure ! Thank you !! :) Tag: Calendar Initialize Tag: 1004826
  - 17
    - Auto insert a vb module to a new workbook -riv Hi all me again lol im looking everywhere for this but cant find it anywhere so was wondering if it was possible. i have a workbook that gets used every week when im done filling in information i have a macro that copies the first three worksheets puts them into a new workbook then attaches the new workbook onto an email this is fine it works perfectly. however im trying to create an auto open for the new workbook i tried installing the auto open on the worksheet itself but this does not work was wondering if i could auto install a module from the folder location and then save the workbook. is this possible? rivers Tag: Calendar Initialize Tag: 1004812
  - 18
    - Workbook size? When you right-click a workbook in windows, and select 'Properties' you can see the size of the workbook in bytes. Is it possible to get a workbooks size from within the workbook by VBA code? TIA, Tag: Calendar Initialize Tag: 1004808
  - 19
    - FOR NEXT Problems ..... Hi there! I really do just bumble my way through this stuff, so I apologise for my lack of technical know-how! I want a procedure (set within a "for .... next") that says: if (a happens) and (b happens) and (c happens) then "Name" & s = (a number, i.e. 5 or 6 or 7 etc) I'm trying to do it like this so I really don't have to post my actual code! I think the problem lies in the ("Name" & s) area!? It will work and give simply "Name" a value, but I need values for Name1, Name2, Name3 etc Hopefully you guys can help without me having to embarrass myself with posting code!? Mike Tag: Calendar Initialize Tag: 1004797
  - 20
    - Paste picture on a userform control Hi everubody, Is there any userform control that we can paste a picture on it during runtime. (right clcik paste) and then use that picture ( contenet) to insert into our worksheet ... I have doubt its possible .. Thanks, -- Best regards, Edward Tag: Calendar Initialize Tag: 1004785
  - 21
    - hiding rows containing formulas which return zero values I use code to hide empty rows on a sheet. This works fine if the cells dont have formulas in them and are empty. However, if I use formulas in those rows which return blank cells, the code doesnt hide the rows. Even though the formulas in the cells return a zero value based upon criteria, the rows wont hide. Here is the code i use: Dim rng As Range On Error Resume Next Range("a28:a54").EntireRow.Hidden = False Set rng = Range("a28:a54").SpecialCells(xlBlanks) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If Can anyone suggest code which will hide the rows if the formulas in cells a28 to a 54 return zero values? Thanks, Roger Tag: Calendar Initialize Tag: 1004783
  - 22
    - copying to powerpoint...error message I execute the following code and the error message "View.Paste: Invalid request. Clipboard is empty or contains data which may not be pasted here." Can anyone figure out why this is happening? Thanks. Here's my code, just assume the variable rngPicture is a range of cells in Excel. Dim P As Object Dim SI As Long Dim S As Object Set P = app.Presentations.Open(Filename:=strFile, ReadOnly:=msoFalse) 'copy the first slide in the powerpoint presentation SI = P.Slides(1).Duplicate.SlideIndex 'set the slide object S equal to the newly added slide Set S = P.Slides(SI) S.Select rng.CopyPicture S.Application.ActiveWindow.View.Paste Tag: Calendar Initialize Tag: 1004768
  - 23
    - How to get variables from worksheet code to userform code In my worksheet, I have buttons to run macros, and I also have a UserForm. In the worksheet code I have: Dim Money as Double Private Sub StartButton_Click() Money = 100 End Sub Then, in the UserForm code: Dim StoreMoney as Double '--I have macros that set the value of StoreMoney Private Sub BuyStuff_Click() If Money >= StoreMoney Then Money = Money - StoreMoney Sheet1.Range("E9") = Money Else: OK = MsgBox("You don't have enough money!", vbOKOnly, "Not enough cash!") End If End Sub The problem is that the userform does not know that Money=100, so it just set's it at zero. How can I tell the userform code to use the variable from the worksheet code? Thanks, Chris Tag: Calendar Initialize Tag: 1004751
  - 24
    - Can't Contenate From Ron de Bruin's site: http://www.rondebruin.nl/mail/folder2/files.htm I tried the following piece of code: With OutMail .To = cell.Value .Subject = "Weekly Forecasting" .Body = "Hi " & cell.Offset(0, -1).Value & "Please view the _ attachment for this week's forecasting." The part after .Value gets truncated. Not sure why though. I tried to put quotes around the whole body, but Excel wouldn't allow that (text turned red). Any ideas as to what I am doing wrong? Thanks, Ryan--- -- RyGuy Tag: Calendar Initialize Tag: 1004737
  - 25
    - Defined names I am not sure what is the best way to go about this but here it goes. I have a spreadsheet that has 108 columns of data A:DD. Each row is a "Cycle" from the machine and the data parameters are in the columns. The first two columns are A the date and time the cycle started and B the cycle number. What I want is in another work book a defined name that includes both A and B. i.e. "9/08/07 12:43:23 PM 67543" (67543 is the batch number) If that is not possible then is there a way when I scroll through the date range the cell beside that shows the cycle number? Once I have that I want to click a button and it would populate the sheet with all of parameters from the cycle. Basically what I want is a selectable Information sheet on each run. Hopefully that makes sense. Please let me know. Tag: Calendar Initialize Tag: 1004733
- Next
  - 1
    - Can I copy data (Values) from one sheet to another with macro Hello all, Iâ??m trying to write a macro. I have 12 identical worksheets, one for each month of the year. Each sheet has a drop down in cell B3 with the 12 months in it which comes from another (13th) sheet. A lot of data gets entered into each monthly sheet and is pretty much the same month to month. When filling out a new month Iâ??d like the user to be able to select a different month with the drop down on the current sheet they are working on and click a macro button that would copy the values from that month to the current worksheet they are working on. I canâ??t seem to get the macro to recognize the value in B3. Help. Tag: Calendar Initialize Tag: 1004725
  - 2
    - Detect if spreadsheet is opened off the web is there anyway to detect if an excel sheet is opened off the web? my requirement is to detect if excel file is directly opened from the web, and if so, I need to provide a message to the user to save to the local machine first. any suggestions?? Tag: Calendar Initialize Tag: 1004719
  - 3
    - Move/reset to the beginning of a TextStream Hello all, I'm storing values in a string array. I need to loop through a TextStream for each element in the array. Dim ptstSource As TextStream Dim pstrMEDates() As String Dim pbytArrayElements As Byte ... 'pstrMEDates() gets ReDim'ed along the way ... For pbytMEDatesIndex = LBound(pstrMEDates) To UBound(pstrMEDates) Do While Not ptstSource.AtEndOfStream ... ... Loop Next pbytMEDatesIndex After running through the TextStream for the first element, ptstSource.AtEndOfStream will be true, correct? It doesn't look like there are any methods for the TextStream object to move to the beginning of the stream. I'm guessing that my only option would be to close the TextStream, then re-open it. Is that correct? Thanks for any help anyone can provide, Conan Kelly --------------------------- "Smokin' weed kills your brain cells. Drinkin' only screws up your liver...ya got 2 a those." - Earl Hickey (NBC's "My Name is Earl") Tag: Calendar Initialize Tag: 1004718
  - 4
    - Return array of page ranges Using Windows XP and Office 2003. Hi, I need a function that will retrieve the range address of each defined page range on a sheet. For example, if page one is set to A2:H50, and page two is set to A51:H80, I need to retrieve each of these sets of ranges. The number of pages may vary at different times. I would prefer to work with an array if possible. Your assistance in the form of example code would be greatly appreciated. Tag: Calendar Initialize Tag: 1004713
  - 5
    - XL2003 VBA: Debuging...can not break or run in break/step mode. Hello all, Something got jacked up on my system last night. I was trying to Break ([Ctrl] + [Pause/Break]) into some long running code so I could step through it. I also set up "Break When Value Is True" watch as well. When I tried to break into the code, I was expecting to see the dialog box with the "End" & "Debug" buttons on it, but nothing....maybe the VBE & XL went into a temp "Not Responding" state, but recovered. After code is finished running/recovered from temp "Not Responding", all of the open XL files can be closed and saved, but XL can't be closed. I get a message box that pops up...something almost exactly like this: MsgBox("Cannot quit Microsoft Office Excel.", vbInformation + vbOKOnly, "Microsoft Excel") ...except that I get that generic .exe/"Not Responding" icon before "MS XL" in the title bar. Maybe I should mention too, I'm working with a TextStream object connecting to a large text file. The TextStream gets closed in the code at the end. Right now if I close everything and kill the XL process in Task Manager, then open up a fresh XL instance and the file I'm working on, I can start the sub procedure I'm working on in break/step mode by hitting [F8]. After hitting [F5] to continue normally, it looks like I can break into the code normally (only on the first time through) up until my "Break When Value Is True" watch should be triggered. My break watch does not get triggered ever, and after that, [F8] won't start step/break mode, it just runs the code (acts like [F5]). [Ctrl] + [Pause/Break] won't break into the code, it acts like Stop/Reset. After the first time through, the code appears to be running normally with [F5] or [F8], I just can't break into it and step through it. Does anyone know what is going on here? Thanks for any help anyone can provide, Conan Kelly --------------------------- "Smokin' weed kills your brain cells. Drinkin' only screws up your liver...ya got 2 a those." - Earl Hickey (NBC's "My Name is Earl") Tag: Calendar Initialize Tag: 1004711
  - 6
    - How do I add nodes to child nodes of a radial diagram? I found this code on MSDN for an org chart and it works fine, but if I change the type to msoDiagramRadial it fails: Dim dgnRoot As DiagramNode Dim shpDiagram As Shape Dim dgnNext As DiagramNode Dim intCount As Integer 'Add organization chart to current document Set shpDiagram = ActiveSheet.Shapes.AddDiagram( _ Type:=msoDiagramOrgChart, Left:=10, _ Top:=15, Width:=400, Height:=475) 'Add three child nodes to organization chart Set dgnRoot = shpDiagram.DiagramNode.Children.AddNode For intCount = 1 To 3 dgnRoot.Children.AddNode Next 'Access the node immediately following 'the first diagram node Set dgnNext = dgnRoot.Children.Item(1).NextNode 'Add three child nodes to the node immediately 'following the first diagram node For intCount = 1 To 3 dgnNext.Children.AddNode Next intCount I wanted the same chart, but as radial. I left the msoDiagramOrgChart so you could see what I originally had. Does msoDiagramRadial support child nodes on it leaves? When I change it above I just get "Permission Denied." Strange error, but I believe it may not support it. Thank you for any help. Tag: Calendar Initialize Tag: 1004710
  - 7
    - Renaming Visible Sheets Only Hi, I am trying to rename only visible charts based on a range from a main tab. Currently I was attempting to do something like the following: For i = 1 To Charts.Count If Charts(i).Visible = True Then Charts(i).Name = range(indirect("Title" & i)).Value End If Next The named range I want for each chart is housed on one main tab and are named "Title1", â??Title2" etc... and each number in the range name matches up with the chart number (so for Chart 6 I would want renamed to the value in range Title6). I see now the indirect function doesn't work, can anyone help me make this work? Thanks K Tag: Calendar Initialize Tag: 1004699
  - 8
    - Unlocking Project When Condition is Met I would like to lock my project and only allow it to be unlocked if the application.username = my name. Can that be done? Tag: Calendar Initialize Tag: 1004696
  - 9
    - merging sheets to master sheet in a certain order I am merging two sheets named "Previous Month" and "Current Month" together on a master worksheet. I want them to sort to the master worksheet in that order. What code do I need to add to my macro to accomplish this? Thanks bunches in advance marcia Tag: Calendar Initialize Tag: 1004678
  - 10
    - Storing multiple ranges using a macro I have to play with lot of ranges in a worksheet. Say range1 is from A1:A10, range2 is from B1:B10 and so on. I have 1000s of such ranges. One of doing it is defining individial ranges say dim range1 as range dim range2 as range .... ... ... etc. I dont want to use 1000 variables for 1000 different ranges. Is there any way of creating an array of ranges? OR Whats the best way of doing it? Tag: Calendar Initialize Tag: 1004674
  - 11
    - charts can you have numbers on your y axis on both sides of the chart? If so how can I do it. I am trying to put english on the Left side and metric on the right side of the graph ....HELP! Eric Tag: Calendar Initialize Tag: 1004672
  - 12
    - How to Save this? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Cannot Save this Workbook...Closing Excel!!!" Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True Application.Quit End Sub -- Ak Tag: Calendar Initialize Tag: 1004668
  - 13
    - Is it possible to programmatically determine where a user is? Hi everybody, Iâ??m writing some VBA code to customize several office programs I know how to find users login name but Iâ??m not sure that is possible to find the region or country they are ? If I can find their country then I can apply some default settings for each region. We have offices in US, Europe and Asia. Thanks, -- Best regards, Edward Tag: Calendar Initialize Tag: 1004663
  - 14
    - Disabling a shape object Hi, have created a SHAPE object, e.g. Set objbtn = .Shapes.AddFormControl(xlButtonControl, 10, 20, 30, 40) However would like it disabled by default - how can I set it to this? Thanks. Tag: Calendar Initialize Tag: 1004661
  - 15
    - SUMIFs for particular cells in blocks of data I have a database export file that includes multiple (1100+) blocks of data in a standard format. Per block, the format is: -- The first column is a merged cell of 9 rows, with the unique identifier for the data block. -- The next five columns form a sub-block of data related to that item in the first column. -- This sub-block has a format of: -- Row 1: Titles of the five columns in the sub-block. -- Rows 2-8: data (column 1 contains the Titles/names for each row). -- Row 9: Totals for each column's worth of data. I want to go through the whole export file (spreadsheet), summing particular cells in Row 9, based on the unique identifer in column 1. Can I use VLOOKUP and SUMIF for this? (If so, how? If not, then how do I find the blocks I want to use for my calculation and then choose the correct cell in Row 9 for that block?) I had thought of taking each row and separately copying it to form a single row per unique identifier, and then using VLOOKUP and SUMIF without the added fun of navigating the sub- block, but my preference is to not have this intermediate reformatting step. A related question: can I use PERCENTILE if I have such non-contiguous blocks of data? Any help/insights/commands/etc. would be greatly appreciated. advTHANKSance.... Tag: Calendar Initialize Tag: 1004658
  - 16
    - Vlookup problem - unable to get the vlookup property Hi I've been reading posts and trying all kinds of things with no luck. Dim Char23PayorName as String Dim SumRW as Integer 'using the current row Char23PayorName = Application.VLookup(Range("A" _ & SumRW).Value, "Payor_Payee!A2:D10000", 4, False) I think the problem is in that the value returned for the lookup value is text and not a number. When debugging, it is showing the lookup value as "Nameabc". The result of this formula will be text. I have restructrued the code to put a formula in a cell. However, the #NAME? error is returned from the formula. The formula in that cell reads: =vlookup(Nameabc, Payor_Payee!A2:D10000, 4, False). If I put quotation marks around the vlookup value in the formula "Nameabc", then it works. Please help while I still remain somewhat sane. Thanks Fred Tag: Calendar Initialize Tag: 1004657
  - 17
    - weird listbox behavior All, I have done a fair amount of Excel VBA programming, but this is a new odd behavior. I have a form with 3 listboxes. All 3 listboxes have a range as a Rowsource. When the form opens they are all populated and display just fine. I want an item selected in the first listbox to force the same selection (i.e. listindex) in the other 2 listboxes. In the listbox1_Click() subroutine I put the following code: listbox2.ListIndex = listbox1.ListIndex listbox3.ListIndex = listbox1.ListIndex v1 = listbox1.Value (or .Text) v2 = listbox2.Value (or .Text) v3 = listbox3.Value (or .Text) v1 and v2 have the right stuff but v3 = "" Any ideas? TIA, Bill Tag: Calendar Initialize Tag: 1004655
  - 18
    - Macro Fails Hello I have a monthly report that has 31 rows of data for each day. The following code is used to display only the current day. The first cell in Column AG for each day contains the date and the other cells for that day in col AG are blank. The code works except for the the 31st of the months that have 31 days. The code is failing on Rows(rng.Row & ":" & rng.Row + 30).Hidden = False and it appears as though the code is trying to hide all rows of the sheet. Does anyone see what may be causing this, and how do I fix it? Private Sub CommandButton1_Click() Dim FindString As Date Dim rng As Range Application.ScreenUpdating = False FindString = Date Columns("AG:AG").Select Selection.EntireColumn.Hidden = False Rows(3 & ":" & Rows.Count).Hidden = False With Sheets("MySheet").Range("AG:AG") Set rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Rows(3 & ":" & Rows.Count).Hidden = True Rows(rng.Row & ":" & rng.Row + 30).Hidden = False Application.Goto rng, True Else MsgBox "Date Not Found" End If End With Columns("AG:AG").Select Selection.EntireColumn.Hidden = True Application.ScreenUpdating = True Range("C1").Select End Sub Tag: Calendar Initialize Tag: 1004654
  - 19
    - error 400, again good day, I have a simple macro that picks up the file and then deletes some columns: Sub test() Dim sap_file As Variant With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False .Show .Execute End With sap_file = ActiveWorkbook.Name Responce = MsgBox(sap_file, vbOKCancel) If Responce = vbCancel Then Exit Sub End If Workbooks(sap_file).Activate Worksheets("Data").Select Columns("H:Z").Select Selection.Clear Rows("1:1").Select Selection.AutoFilter End Sub on Columns("H:Z").select I get error 400. Strange, if I run this portion of macro in the "sap_file" itself everything works. Do you have any idea what is not right? Thanks Serg Tag: Calendar Initialize Tag: 1004651
  - 20
    - Concept question re ranges and calcs I have a macro that does a ton of calculations for me. I am looking to update its capabilities by giving it a user-interface and more flexibility. I am therefore moving out of the realm of doing calculations knowing which column contains what data. So, I now have the situation where columns will be deleted based on user-control and data may or may not be where I originally had it. I'm thinking the best way to handle this is to define columns of data as ranges prior to deleting other columns around them and then doing the calculations on the ranges. Does anyone have some input on if this is the most efficient way to do this? Thanks as always, Matt Tag: Calendar Initialize Tag: 1004647
  - 21
    - Run Time Error 1004: Application Defined or Object Defined Error Greetings All: Trying to rework existing working code. The original and my attempts are shown below. Error shows up at "LastRow = Range............................ If I insert a "Dim Range" statement a completely different error shows up. As you can see, I don't understand this at all. Please help. Option Explicit Sub HoseCarriers_HPTrk_Hide() ' HIDES NON-SELECTED Rows in Specified Range Application.ScreenUpdating = False Dim RowNdx As Long Dim LastRow As Long ' SELECT RANGE OF CELLS TO BE PROCESSED ' A573 thru G621 ' Range("A573:G621").Select ' Original Code ' LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row ' Try to convert to work on Specified RANGE, Not complete W/Sheet. LastRow = Range("A573:G621").Cells(Rows.Count, "B").End(xlUp).Row ' Continue with Original Code For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Hidden = True End If Next RowNdx ' Process Complete - Return all "states" to normal Application.ScreenUpdating = True End Sub Tag: Calendar Initialize Tag: 1004640
  - 22
    - Financial Statement creation Macro from external source I need to create a macro whereby a trial balance can be entered in (or imported) from one Excel spreadsheet, and the result is a balance sheet, income statement, and statement of cashflows on other spreadsheets. Tag: Calendar Initialize Tag: 1004638
  - 23
    - Print to PDF I've read over many of the Print to PDF threads and I seem to be having quite the opposite problem. Most people can't get one pdf from several worksheets but I can't seem to get one pdf per worksheet. What I want to accomplish is to have the macro print out one pdf for each individual worksheet and name the pdf the same name as the worksheet name. I'm sure it's easy I just can't seem to get it to separate the worksheets. Thanks! Tag: Calendar Initialize Tag: 1004634
  - 24
    - Piping debug info to a txt file Hi all, Is there a way to, first, shell out command prompt and create a text file. Then, pipe (append) debug info, such as variable values at different stages into the text file. I tried to do a debug.print, but it gets cut off after a certain number of times. By the time code execution is done, only the end portion appears in the immedidate windows. The top portion is chopped off. Thanks, Ben -- Tag: Calendar Initialize Tag: 1004631
  - 25
    - Graphing Is there away to have two (2) y-axises? I want english measurements on the left and metric on the right. I have both calculated out in cells but am wondering how to make the right side of the graph have anything. Any help would be appreciated. Thank you in advance. Eric Tag: Calendar Initialize Tag: 1004629

Is there any way in which this can be amended so that the date selected when
the calendar is displayed that the next Sunday is selected (unless the
current day is Sunday then I want the date to be the current date)

Private Sub UserForm_Initialize()

Calendar1 = Now()

End Sub

Top

Re: Calendar Initialize by Rick

Rick
Sun Aug 24 14:01:33 CDT 2008

Try this...

Calendar1 = Now + (8 - Weekday(Now)) Mod 7

Rick

"Patrick C. Simonds" <ordnance1@comcast.net> wrote in message
news:uuPdxjhBJHA.1180@TK2MSFTNGP04.phx.gbl...
> Is there any way in which this can be amended so that the date selected
> when the calendar is displayed that the next Sunday is selected (unless
> the current day is Sunday then I want the date to be the current date)
>
>
>
> Private Sub UserForm_Initialize()
>
> Calendar1 = Now()
>
> End Sub

Top

Re: Calendar Initialize by Gary

Gary
Sun Aug 24 14:01:28 CDT 2008

maybe something like this:

If Weekday(Date, vbSunday) = 1 Then
Calendar1 = Date
Else
Calendar1= Date + 8 - Weekday(Date, vbSunday)
End If

--

Gary

"Patrick C. Simonds" <ordnance1@comcast.net> wrote in message
news:uuPdxjhBJHA.1180@TK2MSFTNGP04.phx.gbl...
> Is there any way in which this can be amended so that the date selected when
> the calendar is displayed that the next Sunday is selected (unless the current
> day is Sunday then I want the date to be the current date)
>
>
>
> Private Sub UserForm_Initialize()
>
> Calendar1 = Now()
>
> End Sub

Top