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

Case Select And Loop Procedure

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

Pass Cell In Loop To Procedure

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

Use A Variable To Run Or Call A Procedure By Name

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

Return Variable From Other Procedure

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

VBA Procedure To Loop Through Sheets And Count Non-blank Cells

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

Use Input Box Variable In More Than One Module / Procedure?

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

Keep Variable Values AFTER Procedure Ends

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

Shell Function Compile Error - Expected Procedure Not Variable

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

Loop Through All UserForm CheckBox Controls

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

Create Loop That Changes CheckBox Visibility Based On Value In ComboBox

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

Value For Variable Based Off Control Checkbox.

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

Number 3.5 Is Getting Rounded To 4 In Procedure

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

Store Row Number Within Loop And Delete All Stored Rows After Loop?

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

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

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

Using Same Variable From One Loop To Another?

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

Using A Variable To Set The End Of A For Loop

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

Incorporating Variable Into Loop

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

For Loop With Range Variable

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

Change To Another Variable In Loop?

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

VBA: How To Program A Changing Variable Name In A Loop

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

Good Way To Reset Variable Each Loop To Empty?

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

Macro Loop To Check Several Conditions And With Variable Increments

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

Looping: Run The First Macro An Error Pops Up Saying That A Variable Within The Loop Is Not Set

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

Extra Row Included At Bottom

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

Set Range Variable Using Variable Row Number

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

Copy Information From Variable Workbooks To Master File Then Loop?

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

Checkbox : Married, Widows, Single Checkbox Is Selected

Feb 25, 2009

married, widows, single checkbox is selected

macro solution
good work.

View 5 Replies View Related

Generate A Workbook With Macro Included

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

Unselected Column Included In Print Area?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved