Recognizing Ctl or shift key in VBA

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
    - Uncheck child nodes if parent node is unchecked in treeview I'm trying to use a Treeview control. I'd like to have all child nodes be automatically unchecked if a if I uncheck a parent node. Also, is there a way to specify that if a node contains child nodes it'll automatically be bold? I've got drag 'n drop working on my treeview and so I'd like any parent nodes to bold themselves. I would love it if someone could help me with this. Even better; does anyone know a really, really good and simple to understand novice to advanced tutorial on how to program treeview controls? This would help prevent me from bugging everyone about my treeview woes. Tag: Recognizing Ctl or shift key in VBA Tag: 1014955
  - 2
    - comparing lists I want to compare 2 lists and extract the differences to a third, and indicate why they are different. I have a list of 300+ current employees, but current as of a few months ago. Every few months HR sends a current list as of right now. Is it possible to populate a third sheet by comparing the 2 lists, and having employees on the new list only marked as a new hire, and those on the old list only marked as terminated? I can do it on each sheet separately, but I am trying to be elegant. Thanks, Richard Tag: Recognizing Ctl or shift key in VBA Tag: 1014943
  - 3
    - Determining dates based on colored cells. I'm using Excel 2000. What I'm trying to do is as follows: Lets say I have a row of numbers that are fiscal weeks like this, 1 2 3 4 5 6 7 8 9 10 and i have a row of empty boxes underneath them, which when changed to the color green, i want seperate cells to give the start and end dates of those fiscal weeks. How would I link the fiscal numbers to dates? and what kind of if-then statement would I need for it to calculate a time span? for example: green boxes under fiscal weeks 5 to 7 1 2 3 4 5 6 7 8 9 10 [g][g][g] then have two seperate cells, containing the calculated Start date, and the other with end date. i'd imagine linking the fiscal numbers to real dates would allow excel to add and subtract them. if it's too complex to be answered i understand, thanks ej Tag: Recognizing Ctl or shift key in VBA Tag: 1014934
  - 4
    - using formuals I have a repeating IF statement running down column A for 200 rows. I'm making an assumption that I will not need more than 200 rows ever but I have no idea how many rows I may need. My issue is thta because of the 200 rows being used by the formula when I print the worksheet it prints all 200 rows now just the rows that have real data. If there a way besides having the user set the print area to only print rows with real data?? Tag: Recognizing Ctl or shift key in VBA Tag: 1014931
  - 5
    - Changing up to 2 of 3 vals and the 3rd val is automatically Calcul Hello; Below is a brief description of the problem and the code: There's a starting value in Each of the UNLOCKED cells C12, C14, C16. The purpose of the w/s Change event is (3 scenarios for now): ...If the value in cell C12 is changed manually, cell C16 is automatically calculated ...If the value in cell C14 is changed manually, cell C16 is automatically calculated ...If the value in cell C16 is changed manually, cell C14 is automatically calculated The manually changed values are displayed in dark yellow fill. ==================================== Private Sub Worksheet_Change(ByVal Target As Range) Const sInputCells = "C12,C14,C16" ' manually input ANY 2 of C12, C14, C16 values and the 3rd is automatically calculated ' and displayed in light green fill With Target If Not Intersect(.Cells, Range(sInputCells)) Is Nothing Then If .Column = 3 Then On Error GoTo ErrHandler Application.EnableEvents = False If .Row = 12 Then 'calc C16 .Interior.ColorIndex = 6 .Offset(2, 0).Interior.ColorIndex = 6 .Offset(4, 0).Interior.ColorIndex = 35 .Offset(4, 0).Formula = "=E12*C19*60/C9/C14" ElseIf .Row = 14 Then 'calc C16 .Interior.ColorIndex = 6 .Offset(-2, 0).Interior.ColorIndex = 6 .Offset(2, 0).Interior.ColorIndex = 35 .Offset(2, 0).Formula = "=E12*C19*60/C9/C14" ElseIf .Row = 16 Then 'calc C14 .Interior.ColorIndex = 6 .Offset(-4, 0).Interior.ColorIndex = 6 .Offset(-2, 0).Interior.ColorIndex = 35 .Offset(-2, 0).Formula = "=E12*C19*60/C9/C16" End If End If End If End With ErrHandler: Application.EnableEvents = True End Sub ==================================== With unprotected sheet and only the above w/s Change event in effect ( XL 2003 Options: 1,000 Iterations, Max. change 0.00001, though not really needed!): 1) procedure works fine by manually changing the values in C14 and/or C16 2) procedure works fine by manually changing the value in C12 provided the preceding manual change(s) was in C14 3) procedure FAILS when manually changing the value in C12 if the preceding manual change(s) was in C16. So it seems to me that the problem (circular ref., 0.00 or DIV/0!) is most certainly with the above Change event code. I would be glad to attach the simple test w/b (single w/s), if I only know how !! Your help in identifying the problem would be greatly appreciated. Thank you kindly. Tag: Recognizing Ctl or shift key in VBA Tag: 1014921
  - 6
    - hot get a cell value in varable excel does not support more than seven nested if statements and i have around 20 unique values on which bases i have to fill other filed . for this i want to use case statement in visual basic but unable to get the cell value in a variable if any other option Tag: Recognizing Ctl or shift key in VBA Tag: 1014919
  - 7
    - How to copy a range of cells from one sheet to another I want to copy a set of cells, the values, from one sheet to another in VBA. From "RawData" to "Compare" where x is a row, y is a row and z is a column. These will be changing as the code executes. I have tried this: Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) Compiles, but gives a run time error. So then I tried this: Set rng1 = Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) Set rng2 = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) rng1.Value = rng2.Value Compiles, but I get a run time error at the first Set. Sure, I can create do loops to copy cell by cell, but surely there is a better way. Why do these methods not work? What is a good method to use? Thx Tag: Recognizing Ctl or shift key in VBA Tag: 1014915
  - 8
    - IF Statement I am working with a document that shows pay rate % change for each year for each employee. If they show any number (positive or negative), it means they have had their yearly review. How can I create a total row that shows how many people this past year still need their reviews? I was thinking of using an if statement but I am unsure of how to set it up.Thanks Tag: Recognizing Ctl or shift key in VBA Tag: 1014904
  - 9
    - returning a row number Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold Tag: Recognizing Ctl or shift key in VBA Tag: 1014900
  - 10
    - Bar Graph in Excel 2007 I am using Excel 2007 and am working with a bar graph. I have actual data from Jan. â??06 though Sep â??08. I would like to modify the graph to automatically pick up new months when I add data. Now, each month I edit the data formula to pick up the new month. I tried extending my data range from Oct â??08 to Dec â??09, even though there is no data yet. The graph showed the 15 months without data which is not what I want. Is there a way to make the graph only display the bar if there is data in the data range (not blank)? Thanks, John M Tag: Recognizing Ctl or shift key in VBA Tag: 1014893
  - 11
    - Macro Security I have a excel worksheet that has many macros and the worksheet is quite large. I'm not sure what happened, but all of a sudden the worksheet opens and will not run any macros. It has the security level is high. I check the security level and it's on medium. the worksheet used to ask the "enable" or "disable" macro question when opening the worksheet, but all of a sudden it doesn't. And like I said, the security level is medium, not High. When I open my worksheet I can't run the macros!! Help!!! What can I do to force the enable/disable macro dialog box to come up on opening the worksheet? Tag: Recognizing Ctl or shift key in VBA Tag: 1014892
  - 12
    - Macros I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB Tag: Recognizing Ctl or shift key in VBA Tag: 1014886
  - 13
    - How to make a VLOOK UP OR HLOOK UP formula I have 7 columns and 3 thousand rows, 6 of the columns will be field with numbers. I need to organize the numbers in the cell by rows, not by columns in ascending order. In addition, I need to be able to locate specific numbers in the rows: For Example: find #38 in all the rows and then mark the 7th column next to to the row with a #1 the row that don't have #38 live in blank. THX. in advance Tag: Recognizing Ctl or shift key in VBA Tag: 1014885
  - 14
    - Function to Check if Cell is Highlighted for Change in Shared Work Good afternoon, everyone. I've been banging my head against the wall trying to figure this out for a few days. I am looking to create a shared workbook and utilize the track changes feature. In this workbook I want to write a function that refers to another cell and tells me whether or not that cell is highlighted as being changed (i.e. has the blue triangle in the top left corner). It is extremely important that it refers to whether or not Excel is highlighting it because I want the function result to change if the highlight change date range is changed. I figured out how to have the function check a cell for comments, but since these only look like comment it doesn't work. I'm hoping the VBA language is similar, but for the life of me I can't find how to reference the highlight for change. Any help would be greatly appreciated! Tag: Recognizing Ctl or shift key in VBA Tag: 1014876
  - 15
    - How do I set auto-sort for a column in a worksheet? Hi; I need to create a worksheet to keep record of customers. I have the following example: NAME PHONE ADDRESS CONTRACT WK1 WK2 Phil 123-456 Angel 321-654 John 987-630 I need to keep the first row as the titles row, and need the worksheet to automatically sort in alphabetical order based on the name, and to do it everytime a new customer is added to the list. HOW CAN I DO THAT? Regards, FVO Tag: Recognizing Ctl or shift key in VBA Tag: 1014875
  - 16
    - Show checkbox checked on open I need help. I have 3 userforms. Userform1 has 2 buttons, one to start a new project and one that edits the project as saved. Which ever button is clicked brings up userform2 which has 5 checkboxes. What I would like to happen: when the edit button is clicked I would like the checkboxes that were checked the last time to show up as checked. The value of the checkboxes are stored on sheet3 in cells c2-c6. Thank you for your time and assistance. Tag: Recognizing Ctl or shift key in VBA Tag: 1014846
  - 17
    - ClickMe I've drawn a button - ClickMe on a work sheet and written a little code for it. [I am used to vb code in the Access environment] NOW!! how do I make the button user clickable please? -- Jim Bunton Tag: Recognizing Ctl or shift key in VBA Tag: 1014842
  - 18
    - Application.VLookup I'm trying to programmatically insert comments into columns on multiple worksheets in a workbook. Problem is that it works on 2 of the 3 sheets and not the 3rd. There's a VLookup that matches the data in one column on each sheet by looking for a number. But on the 3rd sheet it craps out. I'm desperate for an answer right now...can anyone help? sPayer = CStr(Application.VLookup(Range("C" & iRow).Value, Range("Payer!C:ZZ"), i - 2, False)) Tag: Recognizing Ctl or shift key in VBA Tag: 1014840
  - 19
    - Excel 2007 and Named Ranges I encountered a problem in 2007 which I believe is a bug. Just wondering if this is a known issue or not. When we programatically delete a row using rows(X).entirerow.delete we noticed that in 2007, that a named range that was a row beneath the deleted row did not shift up. In 2003 and with insert in both editions and going through the Excel UI, named ranges shifted when rows were inserted/deleted, just programatically deleting rows/columns did not shift the named ranges. Am I missing something or is this a bug? -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 Tag: Recognizing Ctl or shift key in VBA Tag: 1014836
  - 20
    - Ribbon Button not Working with my Referenced Workbook I have a workbook named "Global Schedule" that my users open with Excel versions 2003 and 2007. I have a 2003 Add-In that I reference which contains all of the code to manipulate the Global Schedule. If 2003 opens the Global Schedule I have code to add a custom popup menu with some control buttons. These buttons do custom sorts to the Global Schedule. This part works beautifully. If 2007 is used to open the Global Schedule then a 2007 Add-In is only used to change the Ribbon which also contains custom buttons to do my custom sorts. I want these buttons to call the same procedures that are located in the referenced workbook - 2003 Add-In. Can I not call procedures from a referenced workbook in XML? The problem happens when I click the Ribbon control butttons. I get an Error: "Wrong Number of Arguements or Invalid Property Assignment." I checked the XML code in the CustomUI Editor and the buttons onAction has the correct procedure pointing to the 2003 Add-In reference. I'm not sure what this means because my procedures that the control button calls does not require arguements. Does anyone have any ideas? Here is my basic setup: 'GLOBAL SCHEDULE CODE: Private Sub Workbook_Open() If Val(Application.Version) > 11 Then ' open 2007 Add-In which contains XML to change Ribbon Else Call CustomMenuControl(ThisWorkbook) End If End Sub The 2003 Add-In is always open no matter which version of Excel is used because it contains several modules that I reference to manipulate the Global Schedule Data. -- Cheers, Ryan Tag: Recognizing Ctl or shift key in VBA Tag: 1014834
  - 21
    - VLOOKUP FUNCTION Is there a way to return the next largest value using a vlookup function when an exact match is not found? As of now, it keeps returning the value that matches or the next smallest value. Tag: Recognizing Ctl or shift key in VBA Tag: 1014831
  - 22
    - Excel 97 I just got thrown a random assignment at work of making Excel spreadsheet made in 97 work with Excel 2007. I have ZERO experience with any form of VB (I have a C background). First, is there anyway make Excel 2007 backwards compatible with Excel 97 code. This, of course, would be the simplest solution. Second, if I have to update the code, how should I approach this with no VB experience? Should this require drastic changes, a complete rewrite, or only minor fixes? I know this can vary case-by-case, but I am looking for generalities right now. Tag: Recognizing Ctl or shift key in VBA Tag: 1014827
  - 23
    - Copy Formula I have a formula in a cell. The formula is: =150+200 I wish to copy that formula and place it in another workbook as =150+200 and not the resulting value 300. How do I do this? Tag: Recognizing Ctl or shift key in VBA Tag: 1014824
  - 24
    - Should an automated version of Excel close? I'm writing an application in C++ which will read a couple of complicated text files and create an Excel worksheet containing data extracted from these files. In the process of testing, I got to the point where the program: - used CoCreateInstance() to create an instance of Excel and obtain an IUnknown interface - used QueryInterface() to obtain an IDispatch interface - used Invoke() to set the Visible property to TRUE At this point I can see an instance of Excel with no workbook visible on the computer. If I let this test version of the program go ahead and call Release() on each of the interfaces which it has obtained, the instance of Excel closes. This I take to be normal behavior of an automated application. After adding code to get an IDispatch interface to the Workbooks object and calling its Add method, the instance of Excel has a workbook containing three worksheets, just as if it had been started by clicking the Excel button. Now, however if the program proceeds to call Release() on each of the interfaces, Excel does not close. I have to manually close it by clicking its close button. Is this normal behavior? I'm concerned that I may have failed to release an interface although I can't see what that interface might be. I know that I have a IUnknown and IDispatch interfaces to the original Excel object, an IDispatch interface to the Workbooks member, and an IDispatch interface to the Workbook returned by the Add method. The program calls Release() for each of these. Is there anything else I should look for? -- Norm To reply, change domain to an adult feline. Tag: Recognizing Ctl or shift key in VBA Tag: 1014822
  - 25
    - sum in range meeting criteria hi, i have a value in cell A10. i want to sum numbers in range B1:D8 where the value in A10 value is equal to a value in range A1:A9. how can this be done? thanks in advance, geebee Tag: Recognizing Ctl or shift key in VBA Tag: 1014815
- Next
  - 1
    - Selective Ribbon miminise Hi... Is it possible to for a macro to disable the ribbon but keep the quick access toolbar off which I want to use my own toolbar? Cheers Gordon... Tag: Recognizing Ctl or shift key in VBA Tag: 1014813
  - 2
    - Can VB execute VBA code? My VBA macro runs slowly in Excel 2007. Will Visual Basic compile my VBA macro into a faster application that I can somehow use with my Excel spreadsheet? -- TomThumb Tag: Recognizing Ctl or shift key in VBA Tag: 1014811
  - 3
    - lookup returning #N/A i have the formula, =IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densities!O:O,0),2)) This returns #N/A if the F21 string can not be found It there anyway to get it to return 0 or empty insstead? Tag: Recognizing Ctl or shift key in VBA Tag: 1014808
  - 4
    - Excel 2007 Adding Template Sheets and Invalid Name Error (#NAME?) I have a workbook (.xlsm) that gets built by adding in template sheets (.xltx). There are numerous template sheets that can be added depending on the needs of the workbook. Each template sheet refers to values found on the the Master sheet by named ranges. The values on the Master sheet are unique by workbook, therefore the template sheets cannot be linked to the Master template sheet. The formulas on the template sheets use workbook level named ranges to refer to the values on the Master sheet. The ranges on the Master sheet are defined as workbook level named ranges. When I do a Sheet.Add Type:=<path> the template sheet is added to the workbook, however, all the named ranges that refer to the Master sheet show an Invalid Name Error (#NAME?). If I click on a cell with an Invalid Name error and then click in the formula bar and then click on any cell in the workbook, Excel resolves the invalid name. Nothing is changed, just 3 mouse clicks. Any idea why Excel can't resolve the names when the sheet is added? Tag: Recognizing Ctl or shift key in VBA Tag: 1014801
  - 5
    - My Workbook is always hidden when I open it? I don't know what I've done to it. I have on open and before save events, but I can't see the problem. I want the document to open visible. Can you have a look and tell me if there's something I'm missing? Private Sub Workbook_Open() Dim wbk1 As Workbook Dim wbk2 As Workbook On Error Resume Next Set wbk1 = Workbooks("Export_Requests.csv") Set wbk2 = Workbooks("Project Summary.xls") If wbk1 Is Nothing Then ChDir "I:\A&T Contracts\000 Utilities\" Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv") ActiveWindow.Visible = False End If If wbk2 Is Nothing Then ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Option E\Payment\" Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project Summary.xls") ActiveWindow.Visible = False End If End Sub '******* Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim i As Integer Dim sPath As String Dim sFilename As String Application.ScreenUpdating = False Range("A1").Select 'Backup to Flashdrive msg = "Do you want to save a Backup to your Flashdrive?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then On Error Resume Next Set fs = CreateObject("Scripting.FileSystemObject") For i = 67 To 90 Set drspec = fs.GetDrive(Chr(i)) If drspec.drivetype = 1 Then 'MsgBox "Drive " & Chr(i) & " is the removable drive" sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\" sFilename = "IUS Monitor " & _ Format(DateSerial(Year(Date), Month(Date), _ Day(Date)), "dd MM yy") & ".xls" ans = MsgBox("Save File as " & sFilename & "?") If ans = vbOK Then 'Check Path exists If CreateObject("Scripting.FileSystemobject") _ .folderexists(sPath) = False Then MkDir sPath End If ActiveWorkbook.SaveCopyAs sPath & sFilename End If i = 90 End If Next i Else ActiveWorkbook.Activate End If Application.ScreenUpdating = True End Sub 'Thanks 'Dylan Tag: Recognizing Ctl or shift key in VBA Tag: 1014785
  - 6
    - lookup two columns I have two long tables with nanes and numbers next to them Is there a way to look up both tables in the same formula The names colums are C and G The numbers columns are D and H The tables are on a seperate worksheet Densities Looking up what is in cell F21 Tag: Recognizing Ctl or shift key in VBA Tag: 1014783
  - 7
    - Disable selective events In the code below I use Application.EnableEvents = False to prevent the showing of UserForm1 while the code is running. The problem is that it prevents all events from happening. Is there any way to prevent UserForm1 from showing while still allowing other events to happen? Private Sub CommandButton1_Click() Unload End_Of_Month_Disposal Application.EnableEvents = False Application.ScreenUpdating = False ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Clear ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Add Key:=Range( _ "A3:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("2009 Training").Sort .SetRange Range("A2:N5000") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B2").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub Tag: Recognizing Ctl or shift key in VBA Tag: 1014772
  - 8
    - Lookup not working I am using this macro but I have two problems when it runs 1 - If the text being searched for isnt there the it still returns a value which is wrong 2- If the text being searched for is there but the result fiel is empty it returns a zero i need it to leave the cell empty Thank you "=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))" Range("H21").Select Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault Range("H21:H39").Select Range("H21").Select End Sub Tag: Recognizing Ctl or shift key in VBA Tag: 1014770
  - 9
    - lookup returns zero or the wrong number "=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))" Range("H21").Select Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault Range("H21:H39").Select Range("H21").Select End Sub Tag: Recognizing Ctl or shift key in VBA Tag: 1014769
  - 10
    - Error in loading DLL If I open a spreadsheet with a macro and double click on a block and type something then press enter I get the following error Microsoft visual basic error in loading dll. I did check under tools prefrences but none of the library's seems missing. If however I uncheck microsoft office 10.0 object library the spreadsheet works. It only does this on one notebook. Reinstalling office didn't resolve the issue. Any ideas please. Tag: Recognizing Ctl or shift key in VBA Tag: 1014767
  - 11
    - Unable to populate a range with a formula Greetings! This one has me stumped. I am trying to populate a range with a formula. The code is: Sub TestSetAC2() Range("AC2:AC10").Formula = "=SetAC2" Exit Sub End Sub Function SetAC2() SetAC2 = 5 End Function First I opened a blank worksheet. When I execute the procedure, I get #Name? thruout the range. The tool tip just to the left of #Name? says that the formula contains unrecognized text. Any help will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick Tag: Recognizing Ctl or shift key in VBA Tag: 1014756
  - 12
    - Excel vs Lotus [pop-up screen with directories] I'm not sure how to explain my problem. It took some time to convince a friend to take the step to use Excel and convert his Lotus data files. He's about 90% there (unbelievable but true!). He uses a network at his workplace [drive k:\] where the files are stored in various directories [6 major elements] and subdirectories [12 branches and for each year]. His laptop is updatedand, when not connected to the network, he runs a DOS(!!) program which enables him to run the k-drive files on the laptop. A pop-up screen appears with Lotus icons [major directories] if double-clicked another pop-up screen appears with the sub-directories and if selected opens the specific file. He calls this his file organizer and challenged me that its not possible with Excel. I told him that I don't know how, but I know some guys out there [which is you!!] For what it's worth -The DOS file reads: SUBST K:/D SUBST K:C:\ Any form of assistance would be much appreciated. -- HJN Tag: Recognizing Ctl or shift key in VBA Tag: 1014733
  - 13
    - Macro question Hi there, I have two column look like this A 1 2 3 4 B 1 2 C 1 2 3 D 1 2 And I want it to be A 1 A 2 A 3 A 4 B 1 B 2 C 1 C 2 C 3 D 1 D 2 Another word to fill the empty space with the above letter It always start with some letter and number at the top Thanks, Oded Dror Tag: Recognizing Ctl or shift key in VBA Tag: 1014723
  - 14
    - To change command button caption Dear All I am in sheet2. I click a custom tool bar item, then it has to goto sheet1 and change command button in sheet1 by name 'alfa" caption from "go to ballast" to "go to cargo" . Have been getting error when using activesheet.shapes.("alfa").caption= "go to cargo" Pls suggest right code n thnks brgds/captgnvr Tag: Recognizing Ctl or shift key in VBA Tag: 1014717
  - 15
    - ListBox multi select updating in database Hi I am stumped- I have a populated listbox that I need to be able to use to find my selections in a database and enter a defined date into the first empty cell in their row. I honestly have limited VBA experience but anything would help. Basically how do I edit/manipulate the selections from a listbox once I have them selected? Tag: Recognizing Ctl or shift key in VBA Tag: 1014711
  - 16
    - ignoring blanks cells while importing data Hi, I have been importing data from another excel sheet. I use ='C:\pc ranch report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to let it automatically copy all the data from the other excel sheet. However, The data from the other excel sheet is automatically generated, so it might have different number of columns. That's why I dragged the cells down a few more rows than the original excel file has to leave extra space just in case there might be more columns generated in the next generation. The extra space that I dragged shows 0 or #REF. Can I add something in front of the ='C:\pc ranch report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells won't show up as 0 or # Ref?? Thank you so much!!! Tag: Recognizing Ctl or shift key in VBA Tag: 1014706
  - 17
    - How to Select Last Row of Sheet Hello, I have a sub & function where I email the First (column headers) and Last Row of the Sheet. (Sheetname=VoiceMailData) The email is working, and copying the data works fine. I just can't select the First and Last Row. I have 12 columns. Here is my code: Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Cells(Rows.Count, "A").End(xlUp).Row 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible) On Error GoTo 0 Thanks Stephan Tag: Recognizing Ctl or shift key in VBA Tag: 1014704
  - 18
    - Hide VBProject while copying Forms & Modules to another workbook Hi, I have an automated application that modifies a number of workbooks by adding some forms and modules to each one, then saves and closes them. During this process, I use Application.visible = false to hide all the opening/closing workbooks so that the user can do other work without focus jumping to Excel. The problem is that for some reason, the Visual Basic window opens at some point and never closes. This defeats the purpose of making the application visible and I don't know why it's opening the window. It also confuses the user running the application because she doesn't know anything about excel. Any ideas on what command is actually making VBA visible? Any ideas on how to make it invisible? This one's a real bear because if I put a "Stop" or Pause in VBA, VBA automatically becomes visible so there's no way for me to see what's tripping the visiblity property. Thanks, MikeZz Here is the basic routine that copies over the forms and modules: Public Sub SendForms() errMaster = "SendForms: Sending Macros" srcWB.VBProject.VBComponents("frmNotFound").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr srcWB.VBProject.VBComponents("StartupForm").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr srcWB.VBProject.VBComponents("ExportCode").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr Kill sStr Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Const DQUOTE = """" ' one " character Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, " ThisWorkbook.Sheets(" & DQUOTE & "VeryHidden" & DQUOTE & ").Visible = xlVeryHidden" LineNum = LineNum + 1 .InsertLines LineNum, " StartupForm.Show" End With End Sub Tag: Recognizing Ctl or shift key in VBA Tag: 1014696
  - 19
    - If then else if problem for DA I know this macro doesn't work (bad syntax) and I also know there must be more elegant ways to do this. Kindly help. Sub HideUnusedHurdles() If Range("NumberOfHurdles").Value = 4 Then GoTo 100 Else If Range("NumberOfHurdles").Value = 3 Then Range("HideMonthlyRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True GoTo 100 Else If Range("NumberOfHurdles").Value = 2 Then Range("HideMonthlyRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True GoTo 100 Else If Range("NumberOfHurdles").Value = 1 Then Range("HideMonthlyRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True 100 End If End Sub Tag: Recognizing Ctl or shift key in VBA Tag: 1014695
  - 20
    - formatting backcolor on Forms Control Button I was curious how you could color the backcolor of a forms button using VBA. The default is Grey, I would like to color it another color. Thanks -- daniel Tag: Recognizing Ctl or shift key in VBA Tag: 1014683
  - 21
    - Multiple (ByVal Target As Range) Cancel each other out, as written My initial test of the paramenter worked out great!! Then I added the other paramaters. When putting a "1" in cell called TR1_AUG, into parameter 1 parameter 2 obviously cancels it out, so that no ranges are hidden. I currently have 5 parameters of which only ONE may be selected at any time. That one selection causes 8 ranges to be hidden. (although more ranges to hide will likely be required) It is imperative that the DE-selection of the given cell does UN-hide any previously hidden ranges. Can this be done in one go? if so, how? OR should it be set up as 5 independant subs? How would that have to be done? Thank-you If Range("TR1_AUG") = 1 Then 'Single Cell Rows.EntireRow.Hidden = False Range("TR2_PAD_S, TR3_FP16_S, TR4_FP26_S, TR5_FP36_S, Disch2_PAD_S, Disch3_FD16_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If If Range("TR2_PAD") = 1 Then Rows.EntireRow.Hidden = False Range("TR1_AUG_S, TR3_FP16_S, TR4_FP26_S, TR5_FP36_S, Disch1_AUG_S, Disch3_FD16_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If If Range("TR3_FP16") = 1 Then Rows.EntireRow.Hidden = False Range("TR1_AUG_S, TR2_PAD_S, TR4_FP26_S, TR5_FP36_S, Disch1_AUG_S, Disch2_PAD_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If Tag: Recognizing Ctl or shift key in VBA Tag: 1014674
  - 22
    - Loging users who open a file I like to log the users who open spreadsheets that I have developed. What I have done in the past is to run the code below to record the information that I need. Dim Log As Range 'Set the range for the user log Set Log = Sheet6.Range("A2") 'Find the first empty cell Do Until IsEmpty(Log) Set Log = Log.Offset(1, 0) Loop 'Record user name and time opened ActiveCell.Value = fOSUserName() ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() 'Save the changes ActiveWorkbook.Save The workbooks I have typically developed are summary in nature and do not take up much more than half a meg. This process would run and there would a very small delay and the user didn't know there was anything happening. (The fact that I capture this data is common knowlege though.) We just upgraded to Excel 2007 and I have started to develop larger workbooks that I want to track. The lag is enough that it will start to cause problems. Is there a better way to do this? Can I write to an un-opened book or a text-file? I am open to any suggestions. Thanks! PJ Tag: Recognizing Ctl or shift key in VBA Tag: 1014672
  - 23
    - Microsoft Office Web Component issue I am using OWC11 in my application. My application functionality is to open the Excel workbook. I am able to open Office 2003 (Xml Spreadsheet) document using OWC11's AxSpreadsheet object. But the AxSpreadsheet show's last cell of each spreadsheet as merged column. But my original spreadsheet does not contain any merge on last column. I don't have any idea why this is happening. Can any one had come over this issue? Thanks in advance. Tag: Recognizing Ctl or shift key in VBA Tag: 1014669
  - 24
    - How do I do "Range(A1).text" in a XL forumla? I do almost all of my coding in VBA, not Excel, so this one is new to me... I have an external lookup plug-in (BBG) that returns "#N/A" if the lookup fails. I have populated a column in my sheet with these formulas, some of which return #N/A, and others that return numbers. I would like to make a formula that uses the number if it's there, and ignores it if it's #N/A. In code I would simply refer do this: Left(Range(A1).text,1) <> "#" But how do I do this in an XL formula? Maury Tag: Recognizing Ctl or shift key in VBA Tag: 1014664
  - 25
    - can I use an array formula to query a source data table? Is it possible to use an array formula to query an excel source data table? How would the criteria work? -- Please contact me by email if you have an answer to this question. I appreciate it. Tag: Recognizing Ctl or shift key in VBA Tag: 1014651

Does anyone know a way of recognizing whether the shift key or control key
are pressed from inside VBA. I want a userform button to behave differently
if one of these keys are pressed.

Top

RE: Recognizing Ctl or shift key in VBA by BobT

BobT
Tue Oct 07 17:25:00 CDT 2008

Look up in help "KeyDown" and Keycode Constants. It's tricky to explain
here, but the help should be "helpful".

"Rhythm" wrote:

> Does anyone know a way of recognizing whether the shift key or control key
> are pressed from inside VBA. I want a userform button to behave differently
> if one of these keys are pressed.

Top

RE: Recognizing Ctl or shift key in VBA by James_Thomlinson

James_Thomlinson
Tue Oct 07 17:33:01 CDT 2008

http://www.cpearson.com/excel/KeyTest.aspx
--
HTH...

Jim Thomlinson

"Rhythm" wrote:

> Does anyone know a way of recognizing whether the shift key or control key
> are pressed from inside VBA. I want a userform button to behave differently
> if one of these keys are pressed.

Top