PDA

View Full Version : making a "Formula like" userform



AntonioZZZ
07-09-2008, 11:33 PM
hi guys!

here my needs:

i'm trying making a "formula like" userform where the user has to put in some data to execute a macro (the macro needs some range reference to be executed)

i want the userform be like the formula ones, where the textbox can track the address of the selected cells on the sheet.

i tried using workbook_sheetactivate and workbook_sheetselection change events using a mcaro that update the textbox.text with the target address, but it doesn't work...

can anybody hel me? (hope my english is not so bad :-D )

here the code i'm using

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If CalcClass.Visible Then
Call UpdateCalcClass
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If CalcClass.Visible Then
Call UpdateCalcClass
End If
End Sub

Sub UpdateCalcClass()
CalcClass.txt_range.Text = Target.Address
End Sub

Edited by Aussiebear: When supplying code please use the VBA Tag function

TomSchreiner
07-10-2008, 11:07 AM
Hi Antonio. I think this is what you are looking for? Also, look into the RefEdit control. The behavior exhibited by this control is closer to the behavior for cell selections made using the function wizard.

Sub GetUserSelection()
Dim UserSelection As Range

On Error Resume Next
Set UserSelection = Application.InputBox("Please select your range.", Type:=8)

If Not (UserSelection Is Nothing) Then
MsgBox "Selected " & UserSelection.Address
Else
MsgBox "Canceled"
End If
End Sub

AntonioZZZ
07-10-2008, 11:12 AM
Thanks Tom,

i already found that way... ;-)

but i don't like to use inputbox 'cause it doesn't let me add other control on the userform,

i'd like to make a textbox on a userform like the inputbox (type:8),

is there any way to do so???

for the moment i'm using inputbox and it worls fine....but using a custom userform would be much more better

thank you all

TomSchreiner
07-10-2008, 11:24 AM
"Also, look into the RefEdit control." Some people have complained about this control. Iv'e not had any issues with it.

AntonioZZZ
07-11-2008, 05:58 AM
SHAME ON ME!!!!!

how is possible i've never seen that control???

is exactely what i was looking for!!!!

as you can imagine although i'm writing a lot of vba code at work and in my private life...i'm still a newbie in this field...

thanks all...i like this forum very much!!!

it's always useful and i'm learning a lot that is the most important thing!!!!

AntonioZZZ
07-11-2008, 07:00 AM
mmmm

seems ike refedit is too tricky...

i can't make it working fine... :(

i'm going on with the inputbox...it works fine for the moment even if is not so nice to see!!!