Loop The Procedure With The Variable Number Included In The Checkbox Name
Nov 21, 2006
I'm creating a user form that will have 10 checkboxes on it. Depending upon certain conditions being met elsewhere in the workbook, I would like to populate the checkboxes' captions with data from the workbook.
My question is, can I loop the procedure with the variable number included in the checkbox name (well, more to the point, HOW can I loop the procedure...)?
I would like to do something like this:
Dim a As Integer
For a = 1 To 10
With Worksheets("Hi-Tech")
If .Cells(a + 1, 2).Value "" Then
chkHiTech & a.Enabled = True
chkHiTech & a.Caption = .Cells(a+1, 2).Value
lblHiTech & a.Enabled = True
lblHiTech & a.Caption = .Cells(a+1, 2).Value
etc.
View 6 Replies
ADVERTISEMENT
Jun 10, 2007
I am learning about Case Select-
I want to loop through every cell in col. V the range of
i = 6 to LRow
and find any value that equals 13, if it equals 13 then the value in (i,"W") will be "True"
View 9 Replies
View Related
Jan 1, 2008
I want to put some colour in the cells B2, B3, B4, B5, etc and a put black the edge of every box. For do that i make a subrutime that put some color in the range that i want (only if i write "B2" or "B2:B5"), but if i want to put a black edge in all the box i can't do "B2:B5". Then i tried to do something like:
For i=2 To 5 Step 1
Call Boxcute(ActiveSheet.Cells(i,2))
Next i
Sub Boxcute (Box As String)
Range(Box).Selection
etc.
End Sub
But Excel gives me a error with Box and Range.
View 2 Replies
View Related
Sep 8, 2007
I have 46 drop downs that I need to call from a main sub. I want to create one loop that will call each one to perform its function. Below is a simple example of what I am trying to do, but where I need help is with the 'Call Y' line. I am not sure what character to use to tell excel to look at what is in the value of Y, not 'Y' itself
Sub testitout()
Dim y As String
For X = 1 To 2
Sheets(" Lookup"). Range("E3").Value = X + 4
y = "dropdown" & X & "_change()" 'this will return "dropdown1_change()" for the first loop
Call y 'i want to call dropdown1_change() below
Next
View 9 Replies
View Related
Jan 19, 2008
I've search here for a bit and could not find the answer to what I'm looking for. I have 2 procedures on the same worksheet. I need to get the value of a variable from a second procedure so that my first procedure can work with it. From the first procedure I call the second, where the value of the variable is calculated, but when I return to the first procedure, the value of the variable seems to get lost.
View 6 Replies
View Related
Feb 4, 2009
I'm trying to write a VBA procedure that will loop through all the worksheets within my Excel workbook one by one (the number of worksheets in the workbook may vary from month to month) and count all the non-blank cells in Row 12.
If the number of non-blank cells is anything other than 24, I want the procedure to display an error message. (Each individual sheet in the workbook is supposed to contain 24 column headings, and all the column headings are in Row 12).
View 3 Replies
View Related
Apr 10, 2013
i know i have done this before but im totally blanking. I want to user the inputvalue in procedures called from controller, but it just shows as empty.
Code:
Sub controler()
Static inputvalue As Long
inputvalue = InputBox("Please set min size of trades.")
'other macro's called here
end sub
View 4 Replies
View Related
Mar 19, 2007
I have a User Form with 2 groups of 3 radio buttons let's say. The user must choose one button from each group, then press "GO" which runs specific code depending on what buttons they pressed.
How do I make the variables public or static so that after both radio buttons were selected, another seperate procedure in the same module (The GO procedure) can know what selections were made so it can run code based on those selections? In this example, there are only 2 groups of three radio buttons, but in reality I have 30 buttons and it keeps growing so I need the most direct/simple way to solve this.
For Example:
UserForm1
Radio Button Group 1:
Button 1
Button 2
Button 3
Same UserForm1
but now here is Radio Button Group 2:
Button 4
Button 5
Button 6
I need to know which buttons the user clicked from Group 1 and Group 2 after they finished both their selections from each group, and then run code depending on what combination of buttons have been selected. I'm good to go with the If/Then code to decide which code to run, I just cant keep the variables from reading as 'Empty' when I try to see what the user selected when pressing GO and running that procedure.
View 9 Replies
View Related
Aug 8, 2014
I have a VBA shell script that worked just fine on another computer, but now it won't run.
The code that won't execute is:
[Code] ........
View 12 Replies
View Related
Nov 14, 2006
The code listed below checks to see if any checkboxes are checked via a form and if they are then populate a string variable with text to build out a range to be used for a Defined Name Range in excel. The code works but I was wondering if the same functionality could be written better.
Private Sub cmbRun_Click()
Dim SelectedRange As String
If CheckBoxML.Value = True Then
SelectedRange = "Revenue!$M:$M"
Goto jump1
Else
If CheckBoxML.Value = False Then
Goto jump1
jump1:
If CheckBoxSL.Value = True Then
SelectedRange = SelectedRange & ",Revenue!$N:$N"
Goto jump2
Else...............................
View 4 Replies
View Related
May 28, 2014
Trying to create a simple loop that will change the visibility of a large quantity of CheckBoxes when a certain value is selected within a ComboBox . I'm very new VBA programming and loops are not something I've tried yet. My code currently is this:
[Code]......
When I run it, I get a "Compile Error: Invalid qualifier".
View 2 Replies
View Related
Jan 26, 2007
I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file.
for an example:
if checked:
Checkbox1 = NCE1
Checkbox2 = NCE2
Checkbox3 = NCE3
Checkbox4 = NCE4
Checkbox5 = NCE5
I have a bit of code that is like this....
ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls")
I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .
View 9 Replies
View Related
Apr 17, 2009
If you run it type in 3.5. What gets returned from the procedure is the number 4 NOT 3.5. I think its related to the data type declaration but i thought a data type of long would include the number 3.5.
View 5 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Mar 14, 2014
I have a lot of loops, but they are not necessarily nested within each other. Is it possible for me to use the same variable if those loops are not nested.
For example, something like this:
Dim a As Integer
For a=1 To 5
[Lines of code]
Next a
Then I want to just continue using "a" in other loops:
For a=12 To 100
[Lines of new code]
Next a
This way, I don't have to create a new variable every time. Would this be okay? Or is it hard on the memory?
View 2 Replies
View Related
Dec 18, 2008
Is it possible to use a variable to set the end of my for loop.
I only want the loop to continue while each row in worksheet 3 contains data
I have tried this as shown by creating a "TotalRows" Variable as shown in the code but i cant get it working.
View 10 Replies
View Related
Jun 29, 2013
I'm trying to use loop to activate 3 workbooks "OSB1", "OSB2", and "OSB3" and their respective sheets. "OSB1" has sheet "OSB1" in it, workbook "OSB2" has sheet "OSB2" in it etc.
The digit on the end of each OSB is the variable I am using in the loop.
What I am trying to come out with is the following 3 workbooks and sheets being activated:
OSB1.Sheets("OSB1").Activate
OSB2.Sheets("OSB2").Activate
OSB3.Sheets("OSB3").Activate
I've given up the code as I can't work out the inverted commas:
VB:
Dim x As Integer
For x = 1 To 3
OSB" & x & ".Sheets("OSB" & x & "").Activate
Next x
Is it possible to do this at all? The bit after "Sheets" is correct. It's part with the first "x" after the first "OSB" which is incorrect now...
( In my actual code I'm trying to do more than just activate the 3 sheets but need to work out the syntax for this to be able to edit the remainder).
View 9 Replies
View Related
Apr 28, 2014
I'm trying to understand a code from work and I can't get it. I copied only a part of the code here so please don't run it. Also, I have manual inputs in columns L and M (nodes labels such as 1, 2 and 2,3) and section labels on column N (such as BarFT3, BarFT4 etc):
[Code] ..........
What is this loop doing? I just get the first For loop: goes through every lable on colum N (from 1 to ne) but then what?
What is happening to range nudo(n,j)? How works this ">" sign between nudo(n.j) and nn?
View 1 Replies
View Related
Jul 30, 2014
I have a list of Variables Dimentioned a Range. They are sequential; ie. A1, A2, A3, etc. How do I write the code to switch between these? Obviously, I am looking to correctly reference ("A" & aNo).
Code:
For Test = 1 To aNo
With ("A" & aNo)
.Select
[Code]......
View 9 Replies
View Related
May 20, 2006
Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words.
Problem is the variable name changes with each loop, and I can't figure out how to program the name change.
Function TestTranslate(TranslateCell, ProductCell) As String
Dim t As Long
Dim TranslateCeiling As Long
Dim SS1 As String
Dim SS As String
Dim Q, R As String
Dim W As String
Dim LLRString, MLRString, SLRString As String
Dim LRString, MRString, SRString As String
Dim TArray() As String
Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.
View 9 Replies
View Related
Apr 15, 2012
I'm using a variable to loop down a column of data. Each time it loops the variable may or may not perform an operation - it will if there is something in the cell but won't if the cell is empty. It writes the answer of the operation to an ajoining cell, before looping again.
The issue is that if the row cell is empty, what's written to the ajoining cell is the pre-existing value of the variable, because its value hasn't changed.
how best to 'empty' the variable each loop to ensure either that the correct value or nothing at all is written to the ajoining cell?
View 5 Replies
View Related
Oct 20, 2006
Excel file attached! I need a macro to do the following:
Start with cell F4 and read the increment value from cell C4. Then add this with F3 and display the result. Continue with the same increment until it reaches the value equal to cell B4. Then read the increment from cell C5 and do the same until value equals cell B5. Repeat the same step until it reach the value equals B7. I did manually in the column F4 to F28. Moreover, the cell increment will change according to the variable in A2. In this case it is 25 and got 25 values to fill the column R.
View 8 Replies
View Related
Mar 27, 2007
the following code should determine whether the searched value can be found in more than just one row and than enlist certain values from each of those rows in ComboBox4 using a loop. Then I have a second macro which would assign appropriate values from a Sheet to other text boxes whenever one changes the value of the ComboBox4:
Private Sub ComboBox2_Change()
Dim vFind
Dim Firstaddress
Dim rFound As Range
Dim wsName As String
Dim SrchRng As Range
wsName = Me.ComboBox1.Value
With Worksheets(wsName)
vFind = UserForm2.ComboBox2.Value
Set rFound = .Range("B1")
Set SrchRng = .Range("B:B")
Option Explicit
End With...............................
While trying to run the first macro an Error pops up saying that a variable within the loop is not set. I've got no idea how to fix it
View 9 Replies
View Related
Jun 5, 2007
I've got the following code for the attached worksheet. My problem lies at the end of the code where I'm trying to determine the length of characters in column A. I get a zero value after the last row with an actual value.
aLen = Range("A1").End(xlDown).Row - 1
but that bypasses the problem, rather than fix it. By the way, if you run the code, make sure you only have the worksheet "1" in the workbook.
Sub Test()
Application. ScreenUpdating = False
Cells.EntireColumn.AutoFit
'Delete foot symbol from distances
Columns("E:E").Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'Sort & Copy Multipule pages
Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("1").Copy After:=Sheets(1)
Sheets("1 (2)").Copy After:=Sheets(2)
Sheets("1 (3)").Copy After:=Sheets(3)....................
View 2 Replies
View Related
Mar 11, 2008
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View 3 Replies
View Related
Apr 30, 2014
What I have is a master file that needs to pull info from other sources (a simple copy and paste)
those files are called Inventory_xxxx.xlsm . Inside the master file, there are cells with the number of the inventory.
What i need is a way to tell excel to check what number is in the cell, open the files with that number, get the info from the files and paste it back in the master file... and the part that i consider the trickiest, a way to loop it.
For example, lets say i have 2248 in cell A10, it should open Inventory_2248.xlsm copy the contents from cell N4, O4, P4, Q4, R4 and S4(Inventory File) into N10,R10,S10,T10 and U10 (Master file) respectively then in A11, there could be a 2250 so it should open the 2250 file get the info, paste it and so on.....
Now I don't even know if this is do-able or just impossible, been fiddling with various codes to no avail.
View 4 Replies
View Related
Feb 25, 2009
married, widows, single checkbox is selected
macro solution
good work.
View 5 Replies
View Related
Feb 17, 2007
Using either excel VBA or a .NET tool/functionality, is it possible to generate a new excel workbook which contains macros? I need to dynamically create excel files which will have macro functionality already built in.
This should be equivalent to asking how to use VBA or .NET to change/create macros in an existing Excel file.
This is weird, meta-macro stuff, I know. Any help at all would be much appreciated. If any of you know how/if any of this is different in 2007, it'd be amazing.
View 5 Replies
View Related
Feb 14, 2014
Suddenly a file in heavy use for a number of weeks is including an unselected column some 40 columns away in the print area.
View 2 Replies
View Related