See Related Forum Messages: Follow the Links Below to View Complete Thread
Userform With Variable Number Of Textboxes
I desperately need some help.
The situation:
Everyday, I send a new excel sheet with data with x number of rows in it and 5 columns to my colleague. I do not want my colleague to directly interact with the spreadsheet, so my solution is to create a userform.
The problem:
I would like to create a userform to make it easier to modify the data, day by day, by recreating the sheet as an userform. So it would initialize with 5 text boxes across, but an x number of rows. Is there any way to initialize the userform with different number rows everytime? Is there even a method that adds textboxes to a userform?
My train of thought...
Private Sub Userform_initialize
Dim x as integer
Dim y as integer
x= inputbox("how many rows are there?", , , , 1)
y=0
Do until y = x
Userform.add???? .value = Range("A" & y+1)
y=y+1
Loop
Thanks so much for your help!
Create Variable Number Of Dynamic Controls
Ok, On a form I have a variable number of fields, what i want to do is open a new form and pass in the number of fields from the previous form and create a label and textbox for each field on the previous form. i have done this previously, but I added a set ammount of controls. The code I used...
VB Code:
Private WithEvents txtBox1 As TextBoxPrivate WithEvents txtBox2 As TextBoxPrivate WithEvents txtBox3 As TextBoxPrivate WithEvents txtBox4 As TextBoxPrivate WithEvents txtBox5 As TextBoxPrivate WithEvents txtBox6 As TextBoxPrivate WithEvents txtBox7 As TextBoxPrivate WithEvents txtbox8 As TextBoxPrivate WithEvents txtbox9 As TextBoxPrivate WithEvents txtbox10 As TextBox Private WithEvents label1 As LabelPrivate WithEvents Label2 As LabelPrivate WithEvents Label3 As LabelPrivate WithEvents Label4 As LabelPrivate WithEvents Label5 As LabelPrivate WithEvents Label6 As LabelPrivate WithEvents Label7 As LabelPrivate WithEvents Label8 As LabelPrivate WithEvents Label9 As LabelPrivate WithEvents label10 As Label
What I want to do is create these based on the number of fields in the previous form
I want it to do something like this but I am not sure how
VB Code:
dim strField as stringfor i = 1 to clng(me.txtFields.text)strField = "label" & iPrivate WithEvents strfield as labelnext i
I know this code does not work, but it is the general Idea of what I am trying to do. Thanks for any help.
Embedded Controls Vs UserForm Controls
Hello All...
I am (almost done) building a database/spreadsheet program that I have been using a UserForm with controls to manipulate. I would now like to switch to using embedded controls on a sheet within the workbook. The first thing I have noticed in trying to do this is that the embedded controls...(so it seems) can't do anything with a cell range unless it is part of a "Sheets" reference???... even if I select the sheet itself I still can't refrence a cell within the active sheet unless I call the sheet name first. e.g:
Code:
Sheets("Sheet2").Select
[A1].Select
This code gets an object error if I use an embedded control
but works fine with a userform control.
However this works fine for both:
Code:
Sheets("Sheet2").Select
Sheets("Sheet2').[A1].Select
Any Ideas???
Variable Userform Name
I have a combo box in one userform. I assign a variable to the chosen value and then try to open another userform with that variable name. I am obviously making a very simple error, but I can't figure it out. Say the variable is cbChoice then I have tried:
Userform(cbChoice).Show
Userforms(cbChoice).Show
Userform.cbChoice.show
and a few others. Can anyone help me out?
Thanks
Add Variable Textbox To Userform
Hello Every body,
I Want To Add Variable Textbox To Userform in excel with INputbox Command
For example:
If User Input 5 in Inputbox then 5 textbox will be created in userform!
Variable Images On Userform
Hi
I want to create a userform that displays pictures (jpg) which will function as a button. These pictures need to change each time the userform is run (several times during each run of the program). How do I do this?
Currently I'm trying to do this when the userform initializes by specifying the path of the jpeg in the .picture option of image1. But I get a run-time error 424: 'Object required' error message.
Code:
Private Sub UserForm_Initialize()
txtPic1.Enabled = False
txtPic2.Enabled = False
player1 = Sheets("SETTINGS").Cells(10, 1).Value
player2 = Sheets("SETTINGS").Cells(11, 1).Value
player1Pic = Sheets("SETTINGS").Cells(12, 1).Value
player2Pic = Sheets("SETTINGS").Cells(13, 1).Value
txtPic1.Value = player1
txtPic2.Value = player2
txtPic1.TextAlign = fmTextAlignCenter
txtPic2.TextAlign = fmTextAlignCenter
Image1.Picture = player1Pic
Image2.Picture = player2Pic
Image1.AutoSize = True
Image2.AutoSize = True
End Sub
Displaying Variable On A Userform
I have created a template that opens with a userform. The form includes a button that opens a calendar control box so the user can select a date, then the calendar box closes.
Is there any way to display the date that the user has selected on the userform?
Thanks!
Resetting Userform Controls
Hello,
I am trying to figure out if there is a more efficient method of resetting the controls on a userform. My current method is to simply list in a sub each control on the userform and set their values to Empty or False. When needed, I'll call the sub. For example:
Code:
Sub ResetUserform()With Userform1
.Textbox1=Empty
.Listbox1=Empty
.OptionButton1=False
End WithEnd Sub
This works, but it is kind of cumbersome when you have a multipage userform with many controls.
Thanks!
Count The Number Of Checkboxes In Userform
Hi All,
Just one last question for the moment. I have stolen this code from Insomniac (Cheers Dude!!!) on an old post:
Code:
cnt = 0
'Count CheckBoxes
For Each cbx In ActiveSheet.OLEObjects
If TypeName(cbx.Object) = "CheckBox" Then
cnt = cnt + 1
End If
Next
What I want to do is change it so that it will count the number of checkboxes in a userform. I've tried this
Code:
cnt = 0
For Each cbx In userform1.OLEObjects
If TypeName(cbx.Object) = "CheckBox" Then
cnt = cnt + 1
End If
Next
the code doesn't recognise OLEObjects. Is there an alternative command that will work for a userform? If not any other ways I can do this?
Thanks guys, I really appreciate the help.
Passing Variable Derived In Module To Userform
Hi there.
Can anyone please advise me how to pass the value derived in a module to userform?
For instant:
In module1
Code:
sub leave_cal()
worksheets("sheet1").activate
b=range("b1").value
c=range("c1").value
a=b*c
In userform1
Code:
Private Sub CommandButton1_Click()
leave_cal 'call the module
label1.caption=a
Of course, it did not work....label1.caption did not show anything!
But how to get it right?
Cheers.
[Another Question] Problem Of Passing Variable Between Userform
Hello Everyone
I have 2 userforms in my excel file
Userform1 and Userform2
In userform1, there is an array : dim arr(1 to 1000) as double
Now , i would like to pass the arr from userform1 to userform2
in userform2, i use
dim buffer as double
buffer=userform1.arr(1)
But it also failed
is there any method to pass the array between form
P.S. : i need to show the value of the arr in userform1 on the multi-column listbox in userform2
thanks
Search String Among Userform's Controls
Hello everybody!
I have a userform which has 600 checkboxes on it (added at runtime) and I need to implement a search button to help the user reach the desired checkbox quicker then scrooling to it. I don't know how to search the input word through all my checkbox's caption. Anybody can help?
Thanks.
Alex.
Counting The Number Of Cheked Checkboxes In A Userform
Hello,
I need to create a counter that will count the number of checked checkboxes in a userform. When I check a checkbox, I want the counter to increase by 1.
I then need to use IF...Then statements to execute different commands depending on the counter value.
Finally, I need to be able to subtract 1 from the counter later on as I execute more code.
Any help would be appreciated!
Erasing Variable Memory While Keeping Userform Open
Guys
Is there a way of erasing variable memory while keeping a userform open? I have this form that allows the user to pull data, now if I do an Unload or End, the form closes and the user has to reopen the form. I want to enable the user to keep punching in new variables and search criteria without having to relaunch the form everytime they change it. Is there a way where I can clear all the variable memory while keeping the form open?
Cheers
Kenneth
Events For A Dynamic Array Of Controls On A Userform
I'm trying to create an array of controls on a userform which are created at runtime, which I can then reference using an index number to define events.
My problem is that I'm declaring a dynamic array of controls using:
Dim textbox() As Object
and then in the Initialization event for the userform, I have the line:
Set textbox(c) = userform.Controls.Add("Forms.Textbox.1")
to add new textbox controls.
But now, once I have the array of textbox controls, I can't define an event for each of the controls in the array. I.e. the syntax:
Private Sub textbox(c)_Change()gettext = textbox(c).TextEnd Sub
doesn't work because VBA doesn't recognize textbox(c)_Change() syntax.
It has been suggested that I use:
Private Sub textbox_Change(Index as Integer)gettext = textbox(c).TextEnd Sub
but this doesn't work either because VBA doesn't recognize textbox_Change(Index as Integer) as referring to the entire array of textbox controls and the event never fires, at least not the way I've declared the array.
Lastly, using the OnChange property of a control has also been suggested, but it seems like VBA in Excel doesn't recognize it (although it's a legitimate property in VB), e.g. the syntax textbox(c).OnChange = "changesub") isn't recognized.
I have to add controls to the array at runtime and not at designtime because the number of textbox controls in the array depends on an integer variable.
Any advice would be much appreciated.
Last Row Number To Variable 2
Sorry my first message was in-complete
I need a way to write the code that tells a variable what the last row number is that contains Data and put that Row Number Value into a variable. I need to know how to tell the variable to do this. I need a way to tell the variable what the ActiveCell.Row value is. Thanks.
I.E. (Not sure if this is the proper way?)
Dim myCell as Range?
myCell = ?????
Pause Vba Userform, Manual Intput In Excel And Then Again With The Userform ??
hello you all, It's me again
I have had many probs, but the most are gone now, thx to you guys.
Next problem is this:
I use userforms to collect data from files and also user entries.
After a while the user should be able to enter some data manualy in the sheet, but the userform should be still active, because there are some global and other values i would like to keep.
Is it possible??
I would like to put some pause button in vba-userform and on the sheet a button to continue.
Thx 4 the help
Checking If A Variable Is Not A Number.
I am wanting to check a record in a recordset is not a number. I know that some languages have NaN to see if something is not a number.
I can't figure out how to do this in VB. I just want to check if a recordset's data is a number.
Basically, I want to see if it returns null, but there doesn't seem to be a way to do this.
The following ideas that I have tied doesn't work:
Code:
Dim data As Integer
data = rs!partid 'The problem seems to be here... can't assign Null to integer.
If data = Null then
'Do stuff.
End If
If data = "" then
"Do stuff.
End If
If I could check if rs!part number is a number or not, then I would know if it is a number or not... can't figure out how to do that.
Any ideas?
45 Number Long Variable
im setting up a method to use as many tiles as you want but each tile is represented by 3 numbers... so you can imagin how long each line of tiles gets....with 15 tiles in each line...
this is what im doing
first i set the mapline
Code:
mapline(0) = "101101101101101101101101101101101101101101101"
then i read each tile in there by reading that line by three's...
after it reads the 3 numbers from that tile say "101" then it assigns the 3 numbers to 3 varibes X = 1, Y = 0 , B = 1 those are pretty much the x and y and Block of that tile in my picture of tiles... if you are wondering the block is a batch of 7 by 9 tiles below the first batch "0"
now i need to find out how to store the mapline(0) i tryed as string.. but that gives me some mismatch errors later... i have tryed double but that ant right eaither... so if anybody can find out how to mabye convert a string varible to a long or integer varible then please let me know... because by the time it gets down to storing the number for that tile its only 3 numbers but if i can find some way to store the mapline that can still be used to save part of it as a intager or long then that would be great...
Checking To See If A Variable A Number
i have this program that creates a header in my output file for each letter the member of the array starts with.... when a member starts with a number though it creates a heading for each number. is there a way to make a section just for numbers? this is the code i have thus far thanks to some help i received.
For i = 0 To currententry - 1
If MovieList(i).mtype = "DVD Rip" Then
Dim LastLetter As String
Dim ThisLine As String
ThisLine = MovieList(i).Title
If Left$(ThisLine, 1) <> LastLetter Then
LastLetter = Left$(ThisLine, 1)
Print #2, "-----=========[" & LastLetter & "]==========------ "
End If
Dim sWork As String
sWork = Left$(MovieList(i).Title & Space$(50), 50)
sWork = sWork & Left$(CStr(MovieList(i).mtype) & Space(15), 15)
Print #2, sWork
End If
Next i
Text Box Number As A Variable
Hello All,
Is it possible to have a For/Next loop for Text Box numbers?
I tried the following code but I get a error message:
"Object variable or with block variable not set".
Code:
Private Sub Form_Load()
Dim a As Integer
Dim texta As TextBox
For a = 1 To 2
texta.text = 2
texta.text = 4
Next a
End Sub
Any ideas.
Regards
Tarablue
[SOLVED]Chr(number) And Variable
how would i make it say all the Ascii keys only 1 - 130? in the textbox..
i tried this, but didnt know how i would put a varible in the Chr() line
help please, im new to VB6 , heres what i tried, but dont work
i only get 1 character, strange one like | or something....
EDIT: yea, im only getting the Chr(130)
VB Code:
Dim chrnumber As Byte Private Sub Form_Load()chrnumber = 0For i = 1 To 130chrnumber = chrnumber + 1txttest.Text = Chr(chrnumber)Next iEnd Sub
Max Number Of Controls
What is the maximum number of controls that can be placed on a form?
Which are the controls that can be placed on MDI?
Number Of Controls
Does anyone know what the limit is for how many controls you can put onto one form?
Call Controls From Name In Variable
I have a project with alot of controls. It also has a database that is to store informations about each control.
IE: controlname, backcolor, caption
I can read through the DB and have it set variables such as:
Code:
while not rs.eof
contname = rs("controlname")
bcolor = rs("backcolor")
capt = rs("caption")
' need to edit control properties here
rs.movenext
wend
what i am looking for is a way to use contname to set the other properties of the control.
Thank you in advance for any help.
Passing Controls As A Variable
I have 3 control arrays (text box, list box, and combo box)and would like to populate a form in 3 columns. I have never tried to use a control variable before and I am have a problem trying to get it to work. My code is below, if you have any suggestions, please let me know.
Private Sub Form_Load()
Const intSpacing As Integer = 105
Dim intCounter As Integer
Dim ctrlName As Control
For intCounter = 1 To 2
Set ctrlName = lblCoverageType(0)
Load ctrlName(intCounter)
With ctrlName(intCounter)
.Left = ctrlName(intCounter - 1).Left + ctrlName(intCounter - 1).Width + intSpacing
.Visible
End With
If intCounter < 2 Then
End If
Next intCounter
End Sub
Function : Variable Number Of Arguments
I made a string concatenation function for concatenating two strings:
Code:
Private Function StrCon(a as String, b as String) as String
StrCon=a+b
End Function
Later:
Code:
a="Antariksh"
b="Bothale"
msgbox StrCon a,b
Now, this is fine for two strings, but I want a variable number of strings for the function input. I want it to:
msgbox StrCon a,b,c,d,e,,,,,n number of strings
How do I define it in StrCon Function?
Using CallByName With Variable Number Of Arguments
I want to use CallByName to call different functions on an object.
The functions can all have a different number of arguments.
CallByName is defined as:
Function CallByName(Object As Object, ProcName As String, CallType As VbCallType, Args() As Variant)
But when i pass a variant array, an error is generated (Argument not optional)
It works when I pass the arguments one by one, but i need it to work with a variable number of parameters.
The following is a sample to reproduce the problem.
VB Code:
Private Sub Command1_Click()Dim params() As VariantDim Result As Variant ReDim params(1) params(0) = "Test" params(1) = "test" Result = CallByName(Me, "MyFunction", VbMethod, "Test", "Test") '<-- This will work Result = CallByName(Me, "MyFunction", VbMethod, params) '<-- The produces an errorEnd Sub Public Function MyFunction(ByVal a As String, ByVal b As String) As String MyFunction = a & bEnd Function
Send A Variable Containing A Number To Clipboard
I am trying to send a number to clipbaord associated with a control on an image. Below is my code, which gives me an error, can someone pls help... thanks
Private Sub Image2_Click()
Dim p As Integer
p = 0
If Option1.Value Then p = p + 1
If Option4.Value Then p = p + 1
If Option6.Value Then p = p + 1
If Option8.Value Then p = p + 1
'MsgBox "You guessed" & Str$(p) & " out of " & Str$(4)
Clipboard.SetData "p",
Unload Me
End
End Sub
How To Count The Occurence Of The Certain Number In A Variable
hi all,
i have a quite urgent problem to solve
i have the variable in the double format an i need to count the frequency of the appearance of the numbers?
does anybody seen an example of such an algorithm or have any idea how to solve the problem?
thanks in advance
--baronlynx--
Reached Max Number Of Controls
I have reached the maximum number of controls on a form (including many in arrys)- I need to add more!
VB will not let me add another one even as an additional control in an existing control array. Is there a way around this or (more elegantly) can I put additional controls relating to my main form somewhere else?
Number Of Controls On A Form
Is there a way for VB to return an integer value for how many text boxes or labels are on a form?
I wish to use iteration to check for void inputs, and I am assuming I can use syntax within the loop similar to:_
IF txtBox[number]="" THEN
'Error message here'
Thanks
Minimal Number Of Controls
I understand that different machines will have different versions of controls installed if at all installed. And when deploying programs P&D will find the files needed and include them with the package, but that increases the size of the package considerably.
I'm making a program (a installation program like P&D) that will run on all machines, so I'll have to keep the controls that I use as 'standard' as possible to keep the size as small as possible. What are the controls that are included standard with Windows?
Sunny
Number Controls In Form
Hi,all
I've project which need to set 300 pictures in one form , I have tried several times but the form accepted only 244 controls . Please advice me .. what the good way can I set the controls in the form ?
thank you very much
Is There A Way To Transfer Variable Values Between Controls
without having to name the control reference specifically?
Private sub Form1_Click()
call Form2_Load(text1.text)
end sub
Private sub Form2_Load(x$)
Form2.text1.text=x$
end sub
Is there a correct format for doing this? I'm trying to create a plug-in module that will allow me to do the same function over several applications.
thanx
Move Variable Number Of Worksheets In To A New Book!
Hi Everybody
I have a workbook with 'n' number of worksheets. This 'n' is a variable, meaning thereby that the number of worksheets in this workbook is not predetermined - can be 5 or 10 or pick a number!
Of these 'n' number of worksheets in this workbook, I need to move 'm' number of worksheets (there is a criteria that is used to select which 'm' worksheets will need to be moved) in to another workbook. This 'm' can be any number up to a maximum of 'n-1'.
Is there a way to select all these 'm' worksheets that meet the specified criteria (using looping) and then move the entire selection of these worksheets to a new workbook and then save this new workbook.
I have tried to use the 'Sheets(Array()) method but for it to work, the number of variables for the Array need to be predetermined. So I don't think I can use that.
I also tried the 'move' method but I found myself at a loss to specify the path for the new book when specifying value for the 'before' or 'after' argument since the new location is not within the same workbook.
Any suggestions please!
The situation is pretty desperate! So if you can, please throw me a line and I will explore from there!
Best regards
Deepak Agarwal
Random Number Fuction If (variable)= True
Guys,
This question may sound complicated... And I was hoping it's possible to do it. what I wanted is to have random number to change when variable is true or 1, then change the random number can change back to original when variable is false or 0 for example here's snippet:
Code:
Dim Lucky as Byte
Dim LuckyNum as Variant
If Lucky = 1 Then
Lucky = 258 + 2 + 4 - 1
else
Lucky = 258 - 2 - 4 + 1
End If
LuckyNum = Lucky
Print format(LuckyNum, "000" ' If true Print will show 263 otherwise it will go back to number 258
I have tried this similar code using command1 button as if I pressed to be true, and I end up with overflow and could not understand why, Also, I was expecting number to increase sequently in delay time for instance when its true or 1, the display will show 258,260,264 then 263. When its false or 0 it will countdown sequently 264,260,258 and etc. Perhaps, I have done wrong or anything. done search on this site seeing if anyone ever encountered or trying these methods... and end up with no luck. Can anyone help?
Move Number From Select Statement To A Variable
I need help. The following code is suppose to find the highest number in the field "number". How do I get the number I need from "recnum" to the variable "newnum"
Dim newnum As Long
Adodc1.Recordset.MoveLast
Adodc1.Recordset.MoveFirst
Adodc1.RecordSource = "select max(number) as recnum from guest;"
newnum = ?
MsgBox newnum
Total Number Of Records To Be Passed To A Variable
I am accessing Oracle from VB. I just want to pass the value into a variable from VB. Anyone can help me.
eg.:
select count(*) from Customer where City ='TOKYO';
Count(*)
--------
450
I want this 450 to a variable which I defined in VB.
Dim myCount as Integer
set cmdobj = New command
With cmdobj
.activeconnection ="CN"
.commandtext="select count(*) from Customer where City ='TOKYO'"
.execute
end with
mycount = ??
Please help me.
Thanks
my yahoo id is : taurus72dil@yahoo.com
Referring To A Range In Excel Using A Variable Number??
I am currently referring to a specific range in excel using Visual Basic 6.0 as follows:
.Range("A1")
This works fine.
Although I am trying to refer to cells using a variable number within a loop, so I can access a specific cell using a set up variable called num, how would i do this??
I have tried .range(num, 1), though i think this might be for VBA, as it is not working. I am using the variable to refer to a row number btw!
Any help would be great, thanks!
Edited by - ser1alkiller on 12/23/2003 4:35:37 AM
|
TRACKER
