Visual basic for Excel 2

[CharlesM]

How do I use edit boxes in a dialog window to return a text string which I can then place in the cell of a spreadsheet?

 

[dsi]

From the VB Editor,

- Insert a UserForm
- Add a TextBox
- Add a Command Button
- Add this to the UserForm Code

Option Explicit

Private Sub CommandButton1_Click()
    Dim MySheet As Worksheet
    Dim sString As String
    Set MySheet = Sheets("Sheet1")
    
    sString = TextBox1.Text
    MySheet.Range("A1").FormulaR1C1 = sString
    
    Unload Me
End Sub

- Create a macro to open the form

Sub Macro1()
    UserForm1.Show
End Sub

Hope this helps!

 

[SunnyShine]

using

cells(rowindex,colindex).value=TextBox1.text

for handling cell values is more flexible in a programming context. You can also use workbook and sheet reference along with this method.

 

[tstead]

The method DSI posed is an excellent way to maximize the control, look, and feel of the input box - but it is a harder and more time consuming way of doing what you want. I have an easier, but more restricted, way of doing it.

Simply put, if you want to copy the result to cell "A1" then use the following:

range("A1&quot.value=inputbox("Enter the value below&quot

You can put Workbooks("whatever.xls&quot.sheets("Yoursheet&quot in front of range as usual to further define what workbook and sheet you want to copy the cell to.

Another thing, you might want to define the result of the inputbox as a variable that way you can do some checks on it (i.e. if a null stirng was entered, if a number was entered when a leter was expected, etc.) Also, play around with hte inputbox function because you can control the buttons that are displayed (i.e. vbokcancel, vbokonly, vbcancel only, etc.) as well as which one is the default (i.e. vbdefaultbutton1, etc...) You cannot change the label displayed using this method, but it is a really down and dirty simple and easy way of doing what you want.

Regards,
Tim S.

 

[blitz]

How do I select specific fields form an Excel sheet using VBA macros codes, using searh options in my subroutine.

sname = Input ("Please enter the name:&quot
?????
?????

 

[tstead]

What do you mean by "select[ing] specific fields?"

To select a cell, you just use...

range("A1&quot.select

to select cell A1...likewise for any other cel lreference.

If you want to retrieve the value of that cell, then you just assign a variable (lets call it CellVal) in the following way...

CellVal = range("A1&quot.value

Does this help? Is this what you were looking for?

Regards,
Tim S.

 

[Ollie]

I have gotta problem. I need to check the values of two arrays. WHen there is a new value in array1 it should be added into array2. How do I write the comparision code??

Regards

Ollie

 

[Mala]

Can you be more specific?

Are you checking for array sizes only or BOTH array sizes AND respective values?

By 'add' d'you mean the nth value of array be added(+) to nth val in the other array - or the upper bound of the other array be increased by 1 and the new value placed there?

Mala
mala_rs_singh@rediffmail.com

 

[dsi]

You may want to post this as a new thread. DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[LMThron]

Hope you can help me.
Workbook is used for large group.
Sheet tabs, Rows/Columns are all hidden.
Startup worksheet is protected.
Have assigned a macro to an autoshape to unprotect this if an administrator. (ActiveSheet.Unprotect)
The macro is to warn users they need to be an administrator.
Then prompts the ActiveSheet.Unprotect action, which provides an Ok and Cancel button.
I need to define where to go after entering correct password and selecting ok (new sheet, cell A1), where to go if selecting cancel (same sheet, cell A1), and where to go if the password is incorrect (put in a warning message and same sheet, cell A1 or else just remain on the ActiveSheet.Unprotect dialog box).

 

[palusa]

LMThron,
i don't know if i correctly understood your description.

However, i created a workbook (3 sheets -Sheet1, Sheet2 & Sheet3- without tabs and row/col headings) and a startup sheet (sheet1) protected with password and with an autoshape activating a macro (Macro1 in Module1) like:

Sub Macro1()
UserForm1.Show
End Sub

UserForm1 is the ActiveSheet.Unprotect dialog box which contains:

a\ a textbox (tb1) to enter the password
b\ a button OK (cb1) and
c\ a button CANCEL (cb2)

Then this code follows on cb1_click...

Private Sub cb1_Click()
'
   If tb1 = "pwd" Then                   ' admin password is "pwd"
       ActiveSheet.Unprotect ("palusa")  ' startup sheet protect. passwrd
                                         ' is "palusa"
        Sheets("Sheet2").Select
        Range("A1").Select
        '
        '... continue with your code
        '
        Unload Me
  Else
     MsgBox "Invalid Password", vbOKOnly, "Check!"
     tb1 = ""
End If

End Sub

... while for cb2 click...

Private Sub cb2_Click()
    ActiveSheet.Select
    Range("A1").Select
    Unload Me

End Sub

This is very rudimentary.
Hope it helps.

_LF