PDA

View Full Version : msgbox help wanted



lior03
01-16-2007, 04:13 AM
hello
i want to present the following as a msgbox each time i open a workbook.

Sub addinlist()
Dim i As Integer
With Worksheets("addlist")
.Rows(1).Font.Bold = True
.Range("a1:d1").Value = _
Array("Name", "Fullpath", "Title", "Installed")
For i = 1 To AddIns.count
.Cells(i + 1, 1) = AddIns(i).name
.Cells(i + 1, 2) = AddIns(i).FullName
.Cells(i + 1, 3) = AddIns(i).title
.Cells(i + 1, 4) = AddIns(i).Installed
Next
.Range("a1").CurrentRegion.Columns.autofit
End With
End Sub


how can i turn it into a msgbox instead of a sheet?.
thanks

Charlize
01-16-2007, 05:35 AM
You can't format a messagebox (Although I believe there exists an addin to do something extra with a box --- I think Xld gave a link awhile ago ---). Better use a form if you want bold. Use a listbox with 4 columns (if you want color in the listvalues, use a listview).

Or maybe an autoshape on a worksheet (afterwards hidden) that dissapears after 10 seconds.

Charlize

lucas
01-16-2007, 09:44 AM
Do you just want this to happen when you open one specific workbook or when you open any workbook?

lior03
01-16-2007, 11:33 PM
any workbook.

lior03
03-22-2007, 12:07 PM
hello
i want to know in advance if the last cell to the right is empty.if not then go to it .if it is then go to cell A1.

Dim reply As VbMsgBoxResult
If IsEmpty(ActiveCell.End(xlToRight)) Then GoTo err
ActiveCell.End(xlToRight).Select
Exit Sub
err:
If reply = MsgBox("empty cell,proceed anyhow?", vbOKCancel + vbExclamation, "a warning") = vbOK Then
Range("A1").Select
End If


why can't i go to cell A1?.
thanks

mdmackillop
03-22-2007, 02:38 PM
ActiveCell.End(xlToRight) will never return an empty cell unless it's in the last column

lior03
04-12-2007, 02:44 AM
hello
let's take this a little further.i want to force a user to enter number only.
i want to show him the number he selected & then put it in the activecell.
how can i format the msgbox so ef could see 1,000 and not 1000 ?

Sub forceinput()
Dim inputAnswer As Variant
Dim lNum As Single
inputAnswer = Application.InputBox("Enter a number.", "numbers", Type:=1)
If inputAnswer = False Then Exit Sub
lNum = Val(inputAnswer)
If Format(MsgBox("you entered " & lNum, vbInformation + vbYesNo, "only numbers"), "#,##0") = vbYes Then
ActiveCell.Value = lNum
End If
End Sub

thanks

Bob Phillips
04-12-2007, 02:57 AM
You can't with an inputbox. If you want that you will need a custom userform.

moa
04-12-2007, 03:06 AM
Format the variable lNum only, not the whole msgbox.

Also I imagine this would be annoying for users; an input box then a message box just to input one number...

lior03
04-24-2007, 12:28 PM
hello
i am trying to enable a user repeate an action , in this case selecting a rnage for as much as he likes.

On Error GoTo err
Dim tryagain
Dim myrng, r1, r2 As Range
tryagain = True
Set r1 = Application.InputBox("select a range", Type:=8)
Set r2 = Range("C5:D15")
Do While tryagain = True
If MsgBox(" you are in cell " & activecell.Address & " would you like to check intersection ?", _
vbOKCancel + vbQuestion) = vbOK Then
myrng = Intersect(r1, r2)
If MsgBox(" intersection found,try again?", vbInformation + vbOKCancel) = vbOK Then
tryagain = True
End If
End If
Loop
Exit Sub
err:
If MsgBox(" no intersection found,try again? ", vbOKOnly + vbInformation) = vbOK Then
tryagain = True
End If


how can i do this and make the user be able to select a new range each time?