I have a formula that bring me back a figure (cell I3 attached) based on underlying data which works fine until I use the control box I have created to select the other option "poly". If this is checked then I just get an error and I cannot work out why.
View 2 RepliesI am creating a main worksheet (Legend) in which information will be entered and then populate to other areas (worksheets) within the workbook. I would like to create a print macro - that will run and print all the worksheets that we have selected on the "legend" page. These worksheets would have been selected by ticking a check box.
My question is how to associate a check box to a worksheet? For example - the second worksheet is named " Schedule A" 3rd "Schedule B" and so on.
- The check boxes will be name schedule A, Schedule B and so on.
- I would like to associate (link) the checkbox Schedule A with the Worksheet of the same name. Then when this checkbox is selected, and the print macro runs it will print the worksheets that are selected.
I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change I'm learning VBA as quick as I can
View 4 Replies View Relatedcode below whould return value "a" to specified range(s) The cells are formatted Marlett, 10pt, black, bold, center. Should return a nice tick mark to the specified range if selected shape has value of 1. I double chk'd the name of the shape referred to in code and it is correct Yet value of "a" is not returned, though the chkBox is "ticked". What else can I check?
Option Explicit
Sub cv_ReviewCheckBoxes()
Dim wbBook As Workbook
Dim wsNotesLoose As Worksheet
Dim wsCoinRolled As Worksheet
Dim wsCoinLoose As Worksheet
Dim wsSummary As Worksheet
With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
. ScreenUpdating = False
End With.........................
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
I'm using MSCal.OCX to have a user select a date from a calendar in a user form. It all works fine on my machine and the machines of my colleagues. All of these are running XP Pro with Office Pro, Excel version 11.8120.8122. All have SP2 installed.
The problem comes when the customer tries to use it on their machine. They are running XP Pro with Office for Small Businesses, version 11.6560.6568.
I have done a search through Google and I have made sure he has the most up to date version of MSCal (11.0.6650.0) and registered it, using regsvr32 mscal.ocx. I've even made sure he has unregistered and deleted any previous versions. The error message he is getting reads: "Compile Error in Hidden Module: Module 4". This comes up when the calendar is supposed to appear. I have also tried getting the user to register the control in Control Toolbar -> More Controls -> Register Custom Controls but they still get the same error.
I have written the following VBA Script but am getting an error message saying -> "Compile Error. Invalid Next control variable reference".
Code:
Sub Form1() 'Change Yes/No
Dim bottomT1 As Integer
bottomT1 = Sheets("Data Sheet").Range("T" & Rows.Count).End(xlUp).Row
Dim bottomP2 As Integer
bottomP2 = Sheets("Follow-Up").Range("P" & Rows.Count).End(xlUp).Row
Dim bottomO2 As Integer
bottomO2 = Sheets("Follow-Up").Range("O" & Rows.Count).End(xlUp).Row
[code].....
I have a control button on a worksheet that is suppose to call a userform. I am using the code
Frm_Customs.show
But I keep getting an error. The button was created from the control toolbox.
I am having some difficulty with user form navigation bar - when scrolling to the end of the data it works just fine. When scrolling towards the beginning of the data it breaks with a "Runtime 1004: Application-defined or object-defined error".
The reason may be that the beginning of the data is not correctly defined. The data has a header row. Code that I think is relevant to the navigation bar follows:
Private Sub Navigator_Change()
'When Scrollbar value changes, save current record and load
'record number corresponding to scroll bar value
'Call SaveRecord
Set RangeData = Range("CONTACT").Rows(Navigator.Value)
Call LoadRecord
End Sub
Private Sub UserForm_Initialize()
'Load 1st record in CUSTOMER and initialize scroll bar
With Range("CONTACT")
Set RangeData = .Rows(2)
Call LoadRecord
Navigator.Value = 2
Navigator.Max = .Rows.Count
End With
End Sub...........................
I am currently using Excel 2003 SP2 (Standard Edition)
The company where I work has created several userforms which use the Calendar function within VB and all employees can use this.
I have since created a new set of Userforms (which do not use the calendar function) for an external company. Upon opening the spreadsheet and running the code to launch the Userform they get an error message regarding "Calendar Control".
I'm trying to copy an ELOOKUP formula down a column in a sheet. The formula is losing part of the table array when I carry it down and returning an N/A error. Interestingly, it will work when I copy it across a row.
View 9 Replies View RelatedIs there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?
It might be clearer to explain by a dummy code example:
[Code] ......
I'm seeking what I would need to replace Line1 with.
VBA automation -
Excel added the formula - but they all return "Not Found". After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar. In VBA I tried to use the:
objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The Excel Formula bar for the cell is just empty (blank)
Using only the Formula property:
objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The correct formula is created in each cell, but. Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.
In Excel - my vba code successfully constructs these formulas: In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.
1. The example is cell B1 has a drop down list with the following options: MIN, MAX, AVERAGE, MEAN, SUM
The range F2:F20 hold the values upon which I want the formula to work
Cell B3 is to calculate the result of performing the the function defined in B1 over the data set in F2:F20
What formula do I need in B1. I have tried various of methods of forming the above into a text string and then preceding with INDIRECT but my problem seems to be with the range part.
2. Same as above but I want to the users to write as text (without equal sign) the formula they want to use
I have 3 different pivot tables (with 3 different data sources) on 3 seperate sheets (sheet2, sheet3 & sheet4). Each pivot table has a report filter which contains employees names. On sheet 1 I have a sort of 'dashboard' set up feeding from the data in the pivot tables, and a combo box form control from which I can select the employees names.
Currently, if I want to see John's data, I need to go to Sheet1 and select 'John' from the report filter, then goto Sheet2 and select 'John' from the report filter & the same for Sheet 3. I was wondering if it is possible to somehow link the pivot tables to the combo box - so that when I select 'John' from the combo box on Sheet1, it automatically selects 'John' in the report filters on sheets 2,3, & 4.
I have an excel that has dropdown column from form control, in F4 there is a formula of vlookup that take the range from A2 to B20 in consideration but the reference cell that is given to vlookup formula to pick corresponding value is E4 which is also the reference cell of dropdown box and therefore its not showing any name rather than a value, problem with the formula is that when we select any name from dropdown it does not respond to any value in F4 despite changing values in E4.
I am using these setting for the first time.
Image & excel file is attached
drop down.png
I've been working on a spreadsheet to control the 'booking diary' at work and linking it with the movements in and out of our 'chambers'
If you look at Column L & M, they are the volumes and column N is where the 'pallet' is coming / going to .
If you look at line 149 and below I've tried to translate these 'volumes' into movements by time but as you can see it is not to totalled up correctly during certain times.
For example line 158 should total 40 and line 187 should total 80.
Can anyone work it out for me as this happens on numerous lines
I have a range of numbers:
A1=24
A2=17
A3=9
A4=4
Based on this the MEAN=13.5 and STANDARD DEVIATION= 8.81286937760152
I want to create a formula to calculate the UCL and LCL
When I use MINITAB I get UCL=31.23 & LCL=-4.23
I have previous been given the below macro from here that splits a string of text in one cell into groups of 30 and then puts them into several cells, works perfectly.
What I would like to incorpate now is the ability to overide the point at which it splits the text.
i.e. if the inputter puts a "|" (for example) in the original text, this will cause a split in the text and it will restart its 30 count from this point onwards.
Example of what I would like to achieve:
Cell A1 = I would like to change this string of text into groups of 30, where this appears | I would like it to start a new split of 30s from this point on wards and again if another one of these | appears in the text.
Cell B1 = I WOULD LIKE TO CHANGE THIS
Cell C1 = STRING OF TEXT INTO GROUPS OF
Cell D1 = 30, WHERE THIS APPEARS
[Code]....
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies View RelatedI'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
I am using an array from another application for option buttons. I have attempted to adapt this code for checkboxes but with no success.
VB:
Option Explicit
Private Sub cmdadd_click()
Dim wrs As Worksheet, i As Long, x As Long
[Code]......
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
View 3 Replies View RelatedI have a command button and ten checkboxes - one for each row of a list. I need a macro that will delete the rows of every checked checkbox when I press the command button.
The list starts in row 3 and continues to row 12. Checkboxes are checkbox1 for row 3 to checkbox10 for row 12.
In the Attendance Sheet (file attached), I have a list of all students. I like to add students who attend (with "X" marks) each subject onto Sheet "All Records). Date, time, location, instructor, subject to be added respectivley and in accumulate to the Sheet "All Records". I remember I did this before but I can't now.
View 3 Replies View RelatedI want the name of a check box to change when it is checked or unchecked. I have a box where it is working just fine. I was given assistance to the initial code and have been using that code to create the same scenario with other check boxes on my sheet, but receive errors. I have been trying to decipher the problem to no avail. Can someone look at my codes and explain why when I copy the same formula it is not working.
The UN-Check All / Check All button is the working check box. I am trying to apply the same function to the four boxes next to Check Date and GL Post date fields.
I have reset all codes except for the last check box #34, which I have left so the error can be looked at. What I want it to do is when checked I want the name to be "Payroll GL Post Date" When Un-checked the name should be "Adjustment GL Post Date"
I have a code in my workbook that Runs another ceo on close. It is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
EmailOFA
End Sub
What I would like to do is have a checkbox that if checked then this code will NOT run. Can this be done?
In a perfect world what would happen is that once an e-mail was sent once then the checkbox would automatically be checked so that there is no possibility of OFA being e-mailed twice with the same info.
Is it possible to have a message box pop up when a checkbox is checked?
I tried the code as per file attached but cannot work; It requires a direct input into the cell.
When I check the box, I would like the autofilter to come on and select a range & criteria which I have managed. When unchecking the box, the data returns back to normal but the filter stays on the range. When I now check the box with the filter on, the code does not work. I need the filter to stay on if already on.
View 4 Replies View Related