Command Button Help!!!

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
    - VBA Multiple Criteria Lookup I am trying to do a mutiple critera search. If I were using a function it would either look like this: =SUM(IF((Markups!A1:A169=C75)*(Markups!B1:B169=X75)*(Markups!D1:D169=AR75),Markups!E1:E169,0)) or =INDEX(Markups!$E$2:$E$320,MATCH(AR75,IF(Markups!$A$2:$A$320=Actuals!C75,Markups!$D$2:$D$320),0)) I've tried to create a custom function to put the formula in the active cell with no luck. ActiveCell.FormulaArray = "=SUM(IF((Markups!A1:A169=C74)*(Markups!B1:B169=X74)*(Markups!D1:D169=AR74),Markups!E1:E169,0))" (In case you are asking why I am using a custom function for a perfectly good existing function, it's because the custom function evaulates variables passed and performs a different multiple criteria search based on that). Any help would be appreciated! Tag: Command Button Help!!! Tag: 997099
  - 2
    - Clear if "#N/A" and Find End of Range, Fill Blanks Simple problem seems to be turning into a complex problem. I want to clear all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy Tag: Command Button Help!!! Tag: 997098
  - 3
    - Struggling with code modified to delete invalid rows.. I have just one thing that is stumping me. I have modified Ron Debruin code to go accross multiple columns but there are certain columns that I get a Run Time Error 13, Type Mismatch error on. I have column A through K, which are just general numbers, about every other column has up to 2 decimal places. Those columns with decimals are where I am getting the run time error. If I do not incorporate those columns, then the macro runs perfectly. Sub Loop_Example4() Dim Firstrow As Long Dim LastRow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim rng As Range Dim rngColour As Range Dim blnColour As Boolean With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False Firstrow = 2 LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = LastRow To Firstrow Step -1 If .Cells(Lrow, "c").Value <> "No" And _ .Cells(Lrow, "e").Value <> "No" And _ .Cells(Lrow, "g").Value <> "No" And _ .Cells(Lrow, "i").Value <> "No" And _ .Cells(Lrow, "j").Value <> "No" And _ .Cells(Lrow, "k").Value <> "No" Then .Rows(Lrow).Delete Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Tag: Command Button Help!!! Tag: 997093
  - 4
    - Looped Find and Array Doesn't Find Hi everyone/anyone! The following code is supposed to search through a worksheet for a series of values, cut the row with the found value and paste it into a new worksheet. I want it to loop through the array Fnd and go to the Next instance when it fails to find that value in the worksheet. Previous code failed with an Error 91 when it did not find the value. A search pulled up code that used FoundCell. It worked once, but I'm not sure why it doesn't work now (or why it worked in the first place). Right now, FoundCell becomes a value of 1. How do I get FoundCell to equal the results of Cell.Find(What:=Thing...? Sub MoveFind() Dim FoundCell As Range Dim Fnd As Variant Dim SourceSh As Worksheet Dim DestSh As Object Dim Last As Long Set SourceSh = ActiveSheet Worksheets.Add Set DestSh = ActiveSheet SourceSh.Activate ' Range("A1").Select Fnd = Array("&", " or ", " and ","ltd.","employee group","deceased") For Each Thing In Fnd Do Set FoundCell = Cells.Find(What:=Thing, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If FoundCell Is Nothing Then GoTo 1 ' give yourself some feedback Else Rows(ActiveCell.Row).Select Selection.Cut DestSh.Activate Range("A2").Select Last = LastRow(DestSh) Rows(Last + 1).Select DestSh.Paste SourceSh.Activate Selection.Delete End If Loop 1 Next Thing End Sub Tag: Command Button Help!!! Tag: 997091
  - 5
    - Create a new range from existing range Hi, I am writing a user defined function with a range as one of the arguments e.g. N4:N14. While executing the function, I need to find out cells in column x units away e.g. when x = 1 output should be O4:14 or when x =2 output should P4:P14. I need to assign new range to a variable of type range. I am new to VBA. Can you help me? Sachchi Tag: Command Button Help!!! Tag: 997087
  - 6
    - A beginners Elseif question I have a user form I created and it has a few option buttons on it to specify what type of suspension the customer wants. I have it set up so there are two options for spring and two main options for air. The problem I am having is if you select spring first and then decide you want air, the information you enter form there on will effect spring and not air. I put in an Elseif statement on one of the spring options but I am not sure if I need to put one on the other also or if I simply did not write it correctly. Please take a look at what I did and guide me in the right direction Private Sub OptionSpring_Click() Range("B27").Formula = "=L175" Range("J199").Value = "SPRING" Range("J203").Value = "NO LIFT" End Sub Private Sub OptionSprinL_Click() If OptionSpring = False Then ElseIf OptionSprinL = False Then Range("B27").Value = 0 End If Range("B27").Formula = "=L175-K195" Range("J199").Value = "SPRING" Range("J203").Value = "LIFT" End Sub Tag: Command Button Help!!! Tag: 997078
  - 7
    - Workbooks.OpenText - Disable Use of ThousandsSeparator? What would happen if one tried to open a comma-separated textfile that uses decimals (like 1.0023) but no thousand-separators with Workbooks.Opentext - and one didn't give a ThousandSeparator parameter? Since the Default is "," - thus the same as the column-separator, this would surely lead to problems? And in this case, instead of trying to think of a character that is never used in the textfile, would it be possible to *disable* the ThousandsOperator? Thanks & Best Regards, Lars Tag: Command Button Help!!! Tag: 997075
  - 8
    - Copy row to first empty row in new sheet I would like to: 1. Copy a row of data 2. Paste it into the first empty row on another sheet 3. Return to the sheet where the data was originally entered 4. Clear the information previously entered in the aforementioned row Forgive me, but I know little to nothing about VBA language, so an easy-to-understand response is greatly appreciated! Thanks in advance! Tag: Command Button Help!!! Tag: 997074
  - 9
    - Transposing COL A has 165,000 cells that looks like this: CONTROL-NBR 1 DATA1 DATA2 DATA3 CONTROL-NBR 2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR 3 DATA1 CONTROL-NBR 4 DATA1 DATA2 DATA3 DATA4 DATA5 How can I transpose the "DATA" cells for each "CONTROL-NBR" onto the same row as the "CONTROL-NBR" so the result looks like this:: CONTROL-NBR-1 DATA1 DATA2 DATA3 CONTROL-NBR-2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR-3 DATA1 CONTROL-NBR-4 DATA1 DATA2 DATA3 DATA4 DATA5 (Note: The number of "DATA" cells for each "CONTROL-NBR" varies from 1 to 30). Tag: Command Button Help!!! Tag: 997073
  - 10
    - Auto-refreshing results of module-calls Hey all, I have for you today a tricky question which perhaps is harder to pose than answer. First of all, parden me if I am miss-using any of excel terms; my excel is in Norwegian, and hence translating is not always correct. I have a workbook with 4 different sheets. In one of theese sheets the user is supposed to plot in parameters - approx. 100 different types - and several charts. After plotting in these parameters, the charts are meant to be updating themselves while one alters and adapts the parameters in order to get the desired charts. Unfortunately, when one alters the parameters that are the arguements for a function I've defined my self. Like this: * You alter the value of Cell B5 * Cell G5 is =Stoppe(A5,B5,1), where "Stoppe is a function I've written in VBA The Value of G5 is contrary to my desire not updated! This is how I've defined my function: Under Modules of the VBAProject of my workbook, in Moduel 1, I've written Option Explicit Function Stoppe(Pos As Double, Tog As Integer) As Double Dim Ja As Boolean Dim i As Integer For i = 0 To 7 If Pos = Ark4.Cells(2 * i + 6, (Tog - 1) * 3 + 2) Then Stoppe = Ark4.Cells(2 * i + 7, (Tog - 1) * 3 + 2) Ja = True End If Next i If Ja Then Else Stoppe = 0 End If End Function . Does anyone know why the cell calling on this function does not update automatically when it's parameters/arguments are altered? Thanks to anyone with a piece of advice! Sebastian Tag: Command Button Help!!! Tag: 997071
  - 11
    - passing data I have an array that collects data using a userform. How do I pass that array to another userform to use? Thanks Tag: Command Button Help!!! Tag: 997070
  - 12
    - Unable to parameters in MicrosoftQuery I have a quite large sql query that I want to use for fetching data iÂ´nto an Excel sheet. The query returns four columns and I want to use parameters for two of these. My problem is that my sql query cant be displayed grafically in MicrosoftQuery and therefore I am not allowed to use parameters. My question is: What determines if a sql question can be displayed grafically? Pleas enlighten me. Mike Tag: Command Button Help!!! Tag: 997069
  - 13
    - Select Embedded Documents Does anyone know a code that will select only the embedded documents on a sheet? I=92m trying to setup a code that will copy and paste the embedded documents to another sheet, but I can=92t figure out how to keep the code from selecting all the other objects on the sheet. I have Form Buttons, Option Buttons, & embedded PDF, Excel, or Word documents. I only want the code to select the embedded documents. I have tried=85.. ActiveSheet.Shapes.SelectAll I have tried=85.. ActiveSheet.OLEObjects.Select I have tried=85.. Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Type =3D msoEmbeddedOLEObject Then ActiveSheet.Shapes.SelectAll End If Next But, they all select the buttons with the embedded documents. I only want to select the embedded documents. Any help would be greatly appreciated. Tag: Command Button Help!!! Tag: 997067
  - 14
    - using a formula for a column vs. looping over each row - speed difference? Hey again, Does anyone have an idea what the speed differences are between for example: Dim lastRow As Long lastRow = Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row ' (oldWs and newWs are WorkSheets) ' Method 1 oldWs.Range("B2:B" & lastRow).Formula = "If (RC[-1]=""yes"", 1, 0)" newWs.Columns(1).Value = oldWs.Columns(1).Value ' Method 2 Dim i As Long For i = 2 To lastRow If (oldWs.Cells(i, 1).Value = "yes") Then newWs.Cells(i, 1).Value = 1 Else newWs.Cells(i, 1).Value = 0 End If Next i In theory, I'd see Method 1 as doing a redundant operation by calculating the values into a new (later obsolete) column of the old sheet, then copying the values of that, while Method 2 directly calculates the values of the final column. However, I don't know how optimized operations on Ranges are in Excel, so can someone give me an input on which Method would be faster on large datasets, and if Method 1 is faster then by how much? Thanks! Lars Tag: Command Button Help!!! Tag: 997060
  - 15
    - Verifying a worksheet has no dependants I am dealing with a large workbook, created by soeone else - i'm sure many of you have been tehre. Anyway, it seems to me that many of the worksheets contain unused functionality. What would be the best way to determine if no other worksheets were dependant on one worksheet in particular. I thought about using the nuilt in trace dependents / precedents functions, but it doesn't seem reasonable to traverse an entire worksheet, which may have hudreds of thousands of cells within the sued range. The list that i've been going through at the moment is: - search the entire workbook, looking in formulas, for the worksheet name in question. - search the entire workbook for any named ranges that are defined on the the worksheet. - check the VBA code for the two above (slightly trickier, as it may well be referenced but ahve to work out if it is actually used). I'm worried about the length of the list - it seems a bit short, so if any replies on ommissions would be appreciated. And any prompts on a programmatic solution taht people have found useful would be great. Cheers Brz Tag: Command Button Help!!! Tag: 997057
  - 16
    - macro to enter data into different line i have a few data in different line.. and want copy all the data into new sheet using macro. and for next data I wanted the macro to copy to a new line (empty line / next line after first copied data). Let say i click copy button 3 time the data will insert into new sheet in continues line and not replace the same line. thank you in advance Tag: Command Button Help!!! Tag: 997052
  - 17
    - How to write FIFO Function in Excel programming Appreciated if someone can help me on the problem on how to create / write VB code in excel for FIFO function on my stock valuation purpose. and pls mail to :- chchng@transmare-Chemie.com Tag: Command Button Help!!! Tag: 997051
  - 18
    - web box address Hi how do I return a web browser address into a cell as per the following example?? Cells(ActiveCell.Row, "ar").Value = ActiveSheet.Shapes("WebBrowser2").Address Tag: Command Button Help!!! Tag: 997049
  - 19
    - value in range What is the fastest way to check if a range contains a specific value? Tag: Command Button Help!!! Tag: 997040
  - 20
    - Copy & Paste from one Worksheet to another without giving focus? My next problem: I currently have this code to "delete" unwanted lines: ' ************************** Dim wb As Workbook ' wb for workBook Dim ds As Worksheet ' ds for dataSheet Set wb = Workbooks("myWorkbook") Set ds = wb.Worksheets("myWorksheet") ds.Columns.AutoFilter 4, "<>0" ' only show data where column 4 is <> 0 ' (I want to "delete" where column 4 = 0) ds.Rows.Copy ' copy into clipboard ' I'd prefer to give the new worksheet as destination ' and not use clipboard at all ds.Name = "dummy" ' give worksheet a temporary name ' create new worksheet under old name wb.Worksheets.Add().Name = "myWorksheet" ' select position for pasting Worksheets("myWorksheet").Range("A1").Select Selection.PasteSpecial ' paste data & formats Application.DisplayAlerts = False Worksheets("dummy").Delete ' delete old worksheet Application.DisplayAlerts = True ' ************************** Now I would like to change the ds.Rows.Copy and Selection.PasteSpecial to some code that also works in the background, if for example right before Selection.PasteSpecial, someone clicks on a different worksheet - I want to avoid pasting to that then. Any ideas? Best Regards, Lars Tag: Command Button Help!!! Tag: 997037
  - 21
    - track changes hi, does anyone know if it is possible to write a marco to track the changes in an excel document? if so do you know where i could find some information on this or help me write this. i want to create a function button that will create a new page to display all the changes made to a document in say the last month. i am having difficulties making this work as i can not write macros in a shared workbook... thanks, Tag: Command Button Help!!! Tag: 997026
  - 22
    - search for capital letter at end of text string I need to search for a word with a capital letter at the end of the string. For example Hydrochloric acid 1M Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate between lower case and upper case. Can anyone help? Thanks, Roger Tag: Command Button Help!!! Tag: 997023
  - 23
    - Identify what Combobox was changed to update the correct item on the previous Form I am trying to identify a particular Combobox on a form. There is 21 in total(cmb23-43) If a particular value is selected fromt he combobox, then another userform(5) is shown. The user then inputs a numeical value and click OK. Using the commandbutton to OK, i need to identify what combobox triggered the Userform5 to open, so i can then have the correct item updated. EG. If Combobox23 value was changed and triggered the Userform5, then the value entered into Userform5 would then be (Label1.caption = Userform5.Textbox1.value) If Combobox25 value was changed and triggered the Userform5, then the value entered into Userform5 would then be (Label2.caption = Userform5.Textbox1.value) and so on ..................... I do not want to have 21 identical Userform's to input a value, but want to be able to use the same Userform5 for all 21 Comboboxes. How can i do this with the Userform5.CommandButton1_Click Event, to identfy what Combobox was changed to then update the correct label on Userform1 ? Ctm Tag: Command Button Help!!! Tag: 997013
  - 24
    - Excel 2007 Solver Addin Error 400 Hi I'm wondering if anyone else has noticed the following : On loading MS excel 2007, when I go to the data tab, I get a VBA prompt that reads error 400. Apparently the solver add in is missing. So I go back, trace the add in link, and presto it works fine. Now I close out excel, reopen it, only to find the same problem all over again. Any ideas what is going on here? Thanks Chet Tag: Command Button Help!!! Tag: 997012
  - 25
    - setfocus On a userform how do I control where the curser goes when the enter key is pressed? Thanks Tag: Command Button Help!!! Tag: 997009
- Next
  - 1
    - Run-time error `91': Object variable . . . not set Any idea what this error means? It occurs at the following line in my code in subroutine "CreateWPMchart"" ActiveChart.SeriesCollection(3).Select I am running the subroutine FixAndCreateCharts() on a CSV file. The most relevant code may be found below. Thanks, Alan Sub FixAndCreateCharts() ActiveCell.Columns("A:G").EntireColumn.Select Selection.ColumnWidth = 17.71 ActiveCell.Rows("1:1").EntireRow.Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Font.Bold = True FormatPageFileByte Call CreateWPMchart("A:A,C:C,E:E,G:G", "CPU Utilization", "% Processor Time") End Sub Sub FormatPageFileByte() ActiveCell.Range("B:B,D:D,F:F").Select Selection.NumberFormat = "0.00E+00" End Sub Sub CreateWPMchart(ColumnRange As String, ChartName As String, yAxisTitle As String) Worksheets("FIXED_PerfWiz - 5 second Interv").Activate Charts.Add ActiveChart.ChartType = xlLineMarkers MsgBox ("In CreateWPMchart - prior to chart creation, Column Range: " & ColumnRange) ActiveChart.SetSourceData Source:=Sheets("FIXED_PerfWiz - 5 second interv"). _ Range(ColumnRange), PlotBy:=xlColumns ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=ChartName With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = ChartName .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _ "Time (5 sec. intervals)" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = yAxisTitle End With With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasDataTable = False ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(3).Select With Selection.Border .ColorIndex = 10 .Weight = xlThin .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = 10 .MarkerForegroundColorIndex = 10 .MarkerStyle = xlTriangle .Smooth = False .MarkerSize = 5 .Shadow = False End With ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(2).Select With Selection.Border .ColorIndex = 9 .Weight = xlThin .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = 9 .MarkerForegroundColorIndex = 9 .MarkerStyle = xlSquare .Smooth = False .MarkerSize = 5 .Shadow = False End With End Sub Tag: Command Button Help!!! Tag: 997007
  - 2
    - PivotTables AddDataField I'm trying to add a pivot table field using VB Office Tools (I have VB 2007 and Office 2007). In VBA, the command is ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("r3 Ret"), "r3 Ret ", xlAverage In VB, I can't figure out the equivalent constant for 'xlAverage'. I've tried using Help but it doesn't describe this very well. What is the equivalent of VBA's xlAverage in VB? Tag: Command Button Help!!! Tag: 997004
  - 3
    - How do I add additional cities to this drop down? I have a drop down and can not find a sub script that allows me to add additional entries into it. what would I be looking for? I thought I had opened all macros including the form that the drop down is in. A swift kick here might help! using Excel 2000. working a program that I didnt write just trying to update it and get it released. finally got around the link issue and have a good copy to work from. -- DStrider ITIL Certified Tag: Command Button Help!!! Tag: 997003
  - 4
    - PivotTables.AddDataField I'm trying to add a pivot table field using VB Office Tools (I have VB 2007 and Office 2007). In VBA, the command is ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("r3 Ret"), "r3 Ret ", xlAverage In VB, I can't figure out the equivalent constant for 'xlAverage'. I've tried using Help but it doesn't describe this very well. What is the equivalent of VBA's xlAverage in VB? Tag: Command Button Help!!! Tag: 997000
  - 5
    - Reference Label from TextBox I am using a class I found here that works very well for my needs. Now I would like to reference and change the caption of a label of the same name (number) as the text box. The Class: Public WithEvents txtBox As MSForms.TextBox, CtlNum As String Public laBl As String Private Sub Class_Terminate() Set txtBox = Nothing End Sub Private Sub txtBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 46, 48 To 57 If Right(txtBox, 2) = ".5" Then KeyAscii = 0 If KeyAscii = 46 Then SendKeys ("5") ''' These are all number. No Problem Case Else ''' Some other kind of character. Beep and cancel it. Beep KeyAscii = 0 End Select SetLabel End Sub Private Sub txtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 8 Then txtBox.Value = "" End Sub Private Sub txtBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If Len(txtBox.Text) = 3 And Right(txtBox.Text, 2) <> ".5" Then txtBox = Null If Len(txtBox.Text) > 4 Then txtBox = Null End Sub 'Here's where it fails. Sub SetLabel() 'This returns the TextBox number CtlNum = Mid(txtBox.Name, 8, Len(txtBox.Name)) laBl = "Label" & 3 MsgBox UserForm.Name.laBl.Caption End Sub All help will be appreciated. Thank you. Tag: Command Button Help!!! Tag: 996998
  - 6
    - Reference Label from TextBox I am using a class I found here that works very well for my needs. Now I would like to reference and change the caption of a label of the same name (number) as the text box. The Class: Public WithEvents txtBox As MSForms.TextBox, CtlNum As String Public laBl As String Private Sub Class_Terminate() Set txtBox = Nothing End Sub Private Sub txtBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 46, 48 To 57 If Right(txtBox, 2) = ".5" Then KeyAscii = 0 If KeyAscii = 46 Then SendKeys ("5") ''' These are all number. No Problem Case Else ''' Some other kind of character. Beep and cancel it. Beep KeyAscii = 0 End Select SetLabel End Sub Private Sub txtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 8 Then txtBox.Value = "" End Sub Private Sub txtBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If Len(txtBox.Text) = 3 And Right(txtBox.Text, 2) <> ".5" Then txtBox = Null If Len(txtBox.Text) > 4 Then txtBox = Null End Sub 'Here's where it fails. Sub SetLabel() 'This returns the TextBox number CtlNum = Mid(txtBox.Name, 8, Len(txtBox.Name)) laBl = "Label" & 3 MsgBox UserForm.Name.laBl.Caption End Sub All help will be appreciated. Thank you. Tag: Command Button Help!!! Tag: 996997
  - 7
    - How to get source data range for each series of an active chart Is there a way to get (for display) the source data range for each series of an active chart? I cannot find a method to provide the data range. Thanks, Alan Tag: Command Button Help!!! Tag: 996995
  - 8
    - Return ListItem via HitTest Excel 2003 Win XP Need to get a reference to the individual ListItem object upon which the user DragDrops within a target ListView control. Private Sub ListView1_OLEDragDrop[snip] Dim LstItm As MSComctlLib.ListItem Set LstItm = Me.ListView1.HitTest(x, y) The above works however it always returns a reference to the first (Index = 1) ListItem object in the target control, regardless of what the x and y coordinates are. Thanks for any help. Desi Tag: Command Button Help!!! Tag: 996994
  - 9
    - Nested If/Then Help I have a data set of three columns and 6000 rows, these columns represent the last three months of sales actuals. In each column if a sale occurred then a number is in the cell, if not then the cell is blank. In another column I would like to perform a calculation using the most recent data. So if a sale occurred in last month then a number will be in the appropriate cell and I will use it, if the cell for last month is blank then I would like to use the data from the previous month, and so on. I wrote some simple code, but it is not working. The cell locations are correct as the data set is very large. Sub Netback() ' ' Netback Macro ' Macro recorded 7/18/2008 by Sasol NA User Range("bc81").Select ActiveCell.Select Do Until IsEmpty(ActiveCell([0], [-52]).Value) ActiveCell.Offset(1).Select If ActiveCell([0], [-13]).Value > 0 Then ActiveCell.FormulaR1C1 = "=R[0]C[-13]/R[0]C[-37]" End If If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0], [-14]).Value > 0 Then ActiveCell.FormulaR1C1 = "=R[0]C[-14]/R[0]C[-38]" End If If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0], [-14]).Value = IsBlank Then ElseIf ActiveCell([0], [-15]).Value > 0 Then ActiveCell.FormulaR1C1 = "=R[0]C[-15]/R[0]C[-39]" Else: ActiveCell.Value = 0 End If Loop End Sub Help me find my obvious error. Tag: Command Button Help!!! Tag: 996991
  - 10
    - COlor Coding specified time frames I am attemtpting to use validation with date function. Example: I have a booked date and a specimen date. I want the cell to turn a specific color if the booked date and specimen date is 0-1 days, another color for 2-3 days, etc. Is this possible? I can apply conditional formating to a date just one day greater than my booked date. But that is as far as I can get with the cell coloring. Tag: Command Button Help!!! Tag: 996989
  - 11
    - Excel VBA AdvancedFilter Question I'm trying to use the AdvancedFilter option to filter some data. For selecting some search criteria and then filtering it's working fine. Here's my code for that: Sheets("PreFilter").Columns("A:B").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"), CopyToRange _ :=Columns("A:B"), Unique:=True I'd like to search with some exceptions. Ie: I'd like to search for 'pot' without 'potato' coming up. In the Auto-filter, I can do that by saying 'pot' and excluding 'ato.' What is the equivalent for the Advanced Filter? Tag: Command Button Help!!! Tag: 996985
  - 12
    - Excel Macro 'Sheet.Range.Value' Syntax Questions I've got a few very basic syntax questions. On my main worksheet, I've got a list of sheet names. I've saved them as variables using the following commands: Dim ProjectSheet1 As String Dim ProjectSheet2 As String ProjectSheet1 = Sheets(1).Range("C6").Value ProjectSheet1 = Sheets(1).Range("C7").Value I'd like to extract certain information using data from the "Criteria" sheet like this: Sheets("ProjectSheet1").Columns("A:B").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"), CopyToRange _ :=Columns("A:B"), Unique:=True This places the information on the active sheet. That's fine, except that the Sheets("ProjectSheet1") isn't working. If I input the actual sheet name in quotes, everything runs fine. ProjectSheet1 also doesn't work without quotes. Tag: Command Button Help!!! Tag: 996984
  - 13
    - Excel loses mind clearing VBA global variables Iâ??ve inherited a large Excel and Access application that has been grown over time by a number of individual developers. The major issue I need to resolve is the use or abuse of global variables. This application uses VBA global variables rather than storing data on hidden worksheets. Also, all data is loaded into arrays â?? VBA global variables â?? from an Access database when the application starts. There are many places in the code that checks that the global variables are still populated and if not then the application is automatically restarted. I can not redesign and rewrite this application from scratch. I have to find ways to mitigate the risk of a restart. I need a list of things, events, etc. that can cause Excel to clear VBA global variables so I can â??dance aroundâ?? these â??things.â?? Inserting objects onto a sheet may be one thing. Adding and deleting sheets may be another. My problems began with using VBA to insert a combo box onto a sheet right after code that inserted action buttons. I now am using a template and have removed the code that inserts these objects. VBA adds a new sheet using the template. All the objects and formulae work. The data in the global variables is present and is inserted into the new sheet. Now when I click the menu option to do the same process again, all the global variables are cleared. The first step of this process looks for sheets left from previous executions and deletes them. This appears to be the place where Excel resets. Also, Excel announces at the point where a sheet is to be deleted that break mode can not continue and provides an option to continue code execution or end. Is there a way to preserve Excelâ??s state through sheet deletion or should I go through the application and see about reusing sheets rather than deleting them? Tag: Command Button Help!!! Tag: 996977
  - 14
    - Code not working, copy in Select Case section not copying over. Hi friends, The code below is to copy information that matches in one sheet to another sheet. I am having problems with one area where I need it to look at criteria of days. Everything is working fine except this: Select Case Timeline Case 60 .Cells(arr(i), "H").Copy _ Destination:=OutSH.Cells(OutRow, "E") Case 90 .Cells(arr(i), "K").Copy _ Destination:=OutSH.Cells(OutRow, "E") Case 120 .Cells(arr(i), "N").Copy _ Destination:=OutSH.Cells(OutRow, "E") End Select If any of you could look at this, I am sure I have just missed fixing something for it not to pull over, because I don't get a compile error or anything like that. Thanks Entire code: Private Sub CommandButton1_Click() Dim OutSH As Worksheet, TemplateSH As Worksheet, ce As Variant Dim DataCol As Integer, OutRow As Long, i As Long Dim arr As Variant Set OutSH = Sheets("Internal Project Plan") Set TemplateSH = Sheets("Master Template") Dim CriteriaSH As Worksheet Dim Timeline As Long Set CriteriaSH = Sheets("Criteria") Timeline = CriteriaSH.Range("B5") If Timeline <> 60 And _ Timeline <> 90 And _ Timeline <> 120 Then MsgBox ("Incorrect TimeLine") Exit Sub End If For Each ce In Range("B15:B80") If ce = "Yes" Then Dim C As Variant Set C = TemplateSH.Rows("1:1").Find( _ what:=ce.Offset(0, -1).Value, _ LookIn:=xlValues, _ lookat:=xlWhole) If C Is Nothing Then MsgBox ("Could not find : " & ce.Offset(0, -1).Value) Exit Sub Else DataCol = C.Column End If With TemplateSH For i = 2 To 700 If .Cells(i, DataCol).Value = "x" Then 'check to see if it already exists and 'only proceed if it does not If WorksheetFunction.CountIf(OutSH.Range("A:A"), _ TemplateSH.Cells(i, 1).Value) = 0 Then OutRow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row OutSH.Cells(OutRow, "A").Value = .Cells(i, "A").Value OutSH.Cells(OutRow, "B").Value = .Cells(i, "D").Value OutSH.Cells(OutRow, "C").Value = .Cells(i, "P").Value OutSH.Cells(OutRow, "D").Value = .Cells(i, "E").Value OutSH.Cells(OutRow, "I").Value = .Cells(i, "BQ").Value End If End If Next i End With End If Next ce Application.StatusBar = "Transferring Headings" arr = Array(2, 15, 77, 87, 88, 117, 134, 149, 172, 179, 182, 197, 227, 294, 315, 326, 418, 432, 436, 461, 507, 534, 553, 582) OutRow = OutSH.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row With TemplateSH For i = LBound(arr) To UBound(arr) .Cells(arr(i), "A").Copy _ Destination:=OutSH.Cells(OutRow, "A") .Cells(arr(i), "D").Copy _ Destination:=OutSH.Cells(OutRow, "B") .Cells(arr(i), "J").Copy _ Destination:=OutSH.Cells(OutRow, "C") .Cells(arr(i), "E").Copy _ Destination:=OutSH.Cells(OutRow, "D") Select Case Timeline Case 60 .Cells(arr(i), "H").Copy _ Destination:=OutSH.Cells(OutRow, "E") Case 90 .Cells(arr(i), "K").Copy _ Destination:=OutSH.Cells(OutRow, "E") Case 120 .Cells(arr(i), "N").Copy _ Destination:=OutSH.Cells(OutRow, "E") End Select .Cells(arr(i), "BQ").Copy _ Destination:=OutSH.Cells(OutRow, "I") OutRow = OutRow + 1 Next i End With 'sort output data Application.StatusBar = "Sorting Output" With OutSH .Range("A6:J" & (OutRow - 1)).Sort _ key1:=.Range("A6"), _ order1:=xlAscending, _ header:=xlYes End With Application.StatusBar = False Sheets("Internal Project Plan").Select Call Colors Call Module6.SaveAs End Sub -- Danielle :<) Tag: Command Button Help!!! Tag: 996976
  - 15
    - Printing Problem Thanks in advance Problem: Following code would print only a header portion. This is a portion of codes that puts some data into "Statement" worksheet and print it upon different data. For each ----- Worksheets("Statement").Range("A1", "AN4").PrintOut From:=1, To:=1 Next On the other hand, manual File >> Print menu prints everything in a worksheet. I would like to print everthing with a VBA code. Thanks Jae Tag: Command Button Help!!! Tag: 996966
  - 16
    - form submit hi, i have some code that opens up a web page and fills in the user and password fields. then i have some code to submit the form. there is an OK and a CANCEL button in the internet ASP form. here is my code: ... With myDoc.getElementsByName("user")(0) .Value = strUSERNAME End With myDoc.forms(0).submit i noticed that the web page is refreshed and almost looks like it is going to go to another page, but in the end it is just refresh, with the username and password cleared out. what am i doing wrong? i simply need to simulate clicking the OK button. thanks in advance, geebee Tag: Command Button Help!!! Tag: 996961
  - 17
    - Protection Issue I had a spreadsheet that someone else created that was protected. I don't remember exactly what I did but I copied the text and now I wish to protect just parts of it. The parts I wish to allow users to edit are working fine, but some of the rest of the sheet, which SHOULD be protected, are not protected. Does anyone have an idea how to fix this? Only 3 ranges are set to allow users to edit ranges, but SOME cells outside those ranges are not being protected. Ideas? Tag: Command Button Help!!! Tag: 996960
  - 18
    - reference cells related to the current cell In a function, how do I reference values in cells offset from the current cell? For example, I want to concatenate the text in the cells in the two rows above the cell with the function call. Note that the cell containing the function is not necessarily the active cell, so I can't use Activecell to determine it's row/column. I also haven't been able to figure out how to use the Offset function to retrieve the information without also using Activecell. I'm sure this is easy but I just haven't been able to get it. Thanks in advance. CM Tag: Command Button Help!!! Tag: 996948
  - 19
    - Excel 2007 chart question I'm using the following code to set up my series for a 2-axis column / line chart. The first series is for the columns, and the second is for the line. I am able to set the line and marker colors for the second series, but I can't figure out how to set the color for the columns of the first series. You'll see that I even tried to do it by going through each point, but that didn't work either. There seems to be no intellisense for this stuff, and some of the properties don't seem to even be listed in the online documentation (example, Border isn't listed as a property of the Series object here : http://msdn.microsoft.com/en-us/library/bb255883.aspx). The macro recorder doesn't seem to record changes to series formats either. Where exactly should I be finding info on this stuff that is specific to Excel 2007?? With chtChart .ChartType = xlColumnClustered 'Set data source range. ' remove extra series Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop .SeriesCollection.NewSeries .SeriesCollection(1).Values = rngDollars .SeriesCollection(1).XValues = rngQuarters .SeriesCollection(1).Interior.Color = RGB(153, 51, 102) .SeriesCollection.NewSeries .SeriesCollection(2).Values = rngTOs .SeriesCollection(2).AxisGroup = 2 .SeriesCollection(2).ChartType = xlLine .SeriesCollection(2).Border.Color = RGB(0, 0, 128) .SeriesCollection(2).MarkerStyle = xlMarkerStyleDiamond .SeriesCollection(2).MarkerForegroundColor = RGB(0, 0, 128) .SeriesCollection(2).MarkerBackgroundColor = RGB(0, 0, 128) 'set colors Dim intPoint As Integer With .SeriesCollection(1) For intPoint = 1 To .Points.Count .Points(intPoint).Interior.Color = RGB(153, 51, 102) Next End With End With Tag: Command Button Help!!! Tag: 996945
  - 20
    - Application.Intersect Why doesn't this work? Straight from the microsoft site, or the help files for Application.Intersect Worksheets("Sheet1").Activate Set isect = Application.Intersect(Range("rg1"), Range("rg2")) If isect Is Nothing Then MsgBox "Ranges do not intersect" Else isect.Select End If I opened a new workbook, named two ranges which must intersect per the procedure, and it displays the messagebox every time. Doesn't matter if there's a value in the intersected cell or not. Tag: Command Button Help!!! Tag: 996942
  - 21
    - Pivot table refresh problem Hi! I use a macro to modify a pivot table. This line return an errror. ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").CurrentPage = "ABC" The field "ABC" is include in the "Name" column but in the pivot table, I cannot select it. If I go to the field parameter and click to show item without data, "ABC" and a lot of other Names I have already deleted will appear. The "ABC" column is not an empty column. Does anyone know how to completly refresh a pivot field (using macro of not)? Thanks a lot! Alex -- Alex St-Pierre Tag: Command Button Help!!! Tag: 996940
  - 22
    - Trouble with an Array!!! Does anyone know the maximum items you can have in an array? I have 25. When i enter any more i get "Compile error: Expected: expression". Or does anyone know a way around this. I'm trying to write code that will lock and unlock all sheets in a workbook. I have approx 10 more sheets to enter. See code below... "Private Sub CommandButton21_Click() 'UnLock all Sheets Dim mySheetList As Variant Dim sCtr As Long Dim myPWD As String Dim bCtr As Long If Me.ProtectContents _ Or Me.ProtectDrawingObjects _ Or Me.ProtectScenarios Then MsgBox "Contents Protected. You must have Authorization." Exit Sub End If mySheetList = Array("test1", _ "test2", _ "test3", _ "test4", _ "test5", _ "test6", _ "test7", _ "test8", _ "test9", _ "test10", _ "test11", _ "test12", _ "test13", _ "test14", _ "test15", _ "test16", _ "test17", _ "test18", _ "test19", _ "test20", _ "test21", _ "test22", _ "test23", _ "test24", _ "test25") myPWD = "test" For sCtr = LBound(mySheetList) To UBound(mySheetList) With Worksheets(mySheetList(sCtr)) '<---added (sctr) here! For bCtr = 20 To 21 .OLEObjects("Commandbutton" & bCtr).Visible = True .Protect Password:=myPWD, DrawingObjects:=False, _ Contents:=False, Scenarios:=False ActiveWindow.DisplayWorkbookTabs = True Next bCtr End With Next sCtr End Sub Tag: Command Button Help!!! Tag: 996938
  - 23
    - Find A value in a column and count the rows I am not making this up I have been asked to do the following: Create in VBA such that the code finds a specified date in Column A and then counts the Number of rows to it and then plugs that number into an Hlookup. So then the Hlookup goes to that row and gets the appropriate value. so far i have the following: Step 1: Find in Column A the specified date that might be typed in a cell somewhere else, say on the target spreadsheet. I'm sure i can do this part. Step 2: count the rows from the top in which the date is found? actually now that i think about it count the rows from the top of the Hlookup. I'm sure i don't know how to do this. Step 3 how do i plug this number into an Hlookup. do i just set the row count equal to r and then stick r in the formula? if anyone has anygood ideas or better ways of doing the same I'm all ears. Thank you, Karl Tag: Command Button Help!!! Tag: 996937
  - 24
    - Need a Macro to create a Pivot where fields may change I recently found out that I could host an Excel spreadsheet on the web and have filters set by each user. The filters are named by the user and then saved on their PC in the hkey_local_machine directory by some vb code so they do nt need admin rights. This way every time the user comes back to the public file they will see their custom filters. I got this to work fine, but I want to pivot the data because it is headcount data that needs to be summarized. There are 11 fields that the count can be filtered by and each field can be displayed or not using another macro. So the field list will be different each time. I need a macro that will pivot whatever data happens to be chosen. I can create a macro that copies the filtered data to another sheet and then pivots all the fields but it hard codes the field names I pick of course. I need it to give variable names to the fields, like field (1), field (2), etc and then pivot them in the order they are listed from left to right. If the user wants to change the order after it is pivoted then that is their perogative. Here is the pivot macro so far" Selection.Copy Sheets("Pivot_source").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Pivot_source!R1C1:R41C11").CreatePivotTable TableDestination:="", TableName _ :="PivotTable4", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:=Array("GROUP", _ "DIVISION", "Program", "STATE", "CITY", "UNION_CODE"), ColumnFields:= _ "PAY_CATEGORY" ActiveSheet.PivotTables("PivotTable4").PivotFields("Emplid_Cnt").Orientation = _ xlDataField End Sub I need Group etc to be field (1) and so on. Tag: Command Button Help!!! Tag: 996933
  - 25
    - how do i use a macro I am looking for a macro that will run through a selected range of cells that will stop at each cell and wait for a keyboard button to be pressed, and depending on the button pressed will do different types of formatting. Can anyone help. I don't want this to work with a Dialog box or anything, I want it to work with keyboard buttons. For example the first cell in the range it waits for me to press a button. I press the b button and it bolds it. The second cell in the range it waits for me to press a button. I press the i button and it puts the cell in italics. And so on.. Tag: Command Button Help!!! Tag: 996928

Does anyone know if you can have a Command Button on a Chart page?
When I copy and paste a Command Button to a Chart page the Command
Button looses all of it properties. I have a macro written where there
must be a Command Button on each page in order for it to work. About
2/3 of the sheets in my workbook are Charts.

Top

RE: Command Button Help!!! by James_Thomlinson

James_Thomlinson
Tue Jul 22 10:13:03 CDT 2008

Chart sheets do not support embedded objects. To that end you can not use
controls from the control toolbox. You can however use buttons from the forms
toolbar.
--
HTH...

Jim Thomlinson

"Keith" wrote:

> Does anyone know if you can have a Command Button on a Chart page?
> When I copy and paste a Command Button to a Chart page the Command
> Button looses all of it properties. I have a macro written where there
> must be a Command Button on each page in order for it to work. About
> 2/3 of the sheets in my workbook are Charts.
>

Top