Newbie VBA questions

[Brian Vallelunga]

I'm very new to Excel VBA. I've been working on a spreadsheet for the
past few days and I have a few unanswered questions. Hopefully someone
can answer them for me here.


1) In VBA, how do I create a string that includes a date? I want to set
the value of a field to be something like "Sunday 10/3". I want to do
this starting with a given date, and then going through an entire week.
I figured out I could do:

Dim currentWeekSunday As Date
currentWeekSunday = "10/3/2004"
ActiveSheet.Range("E6").Value = "Sunday " & currentWeekSunday


That code works, but it formats the date as "10/3/2004" and I want
"10/3" only.



2) Using the forms toolbar, I added a combobox to my sheet. I have
assigned the values of this combobox to a range of cells on another
sheet. I want to get the value of the combobox from within code. How do
I go about this? Can I name the combobox ? I'm a C# developer, and I'm a
bit lost in Excel.


3) I successfully have a button that inserts or refreshes a basic web
query. How do I go about passing parameters to the web query from within
code? Specifically, I need the value from my combobox in question #2 to
be the parameter.

Thanks for any and all help.

Brian

 

[Jim Rech]

If you're going to hard-write the date in your code then you can just do
this:

Range("E6").Value = "Sunday 10/3"

Another option is to use a date format to show the day of the week:

With Range("E6")
.Value = DateValue("10/3")
.NumberFormat = "dddd m/d"
End With

I'd suggest that you ask one question per post.

--
Jim Rech
Excel MVP
| I'm very new to Excel VBA. I've been working on a spreadsheet for the
| past few days and I have a few unanswered questions. Hopefully someone
| can answer them for me here.
|
|
| 1) In VBA, how do I create a string that includes a date? I want to set
| the value of a field to be something like "Sunday 10/3". I want to do
| this starting with a given date, and then going through an entire week.
| I figured out I could do:
|
| Dim currentWeekSunday As Date
| currentWeekSunday = "10/3/2004"
| ActiveSheet.Range("E6").Value = "Sunday " & currentWeekSunday
|
|
| That code works, but it formats the date as "10/3/2004" and I want
| "10/3" only.
|
|
|
| 2) Using the forms toolbar, I added a combobox to my sheet. I have
| assigned the values of this combobox to a range of cells on another
| sheet. I want to get the value of the combobox from within code. How do
| I go about this? Can I name the combobox ? I'm a C# developer, and I'm a
| bit lost in Excel.
|
|
| 3) I successfully have a button that inserts or refreshes a basic web
| query. How do I go about passing parameters to the web query from within
| code? Specifically, I need the value from my combobox in question #2 to
| be the parameter.
|
| Thanks for any and all help.
|
| Brian

 

[Tom Ogilvy]

Dim currentWeekSunday As Date
Dim i as Long
currentWeekSunday = DateValue("10/3/2004")
for i = 0 to 6
With ActiveSheet.Range("E6").Offset(i,0)
.Value = currentWeekSunday
.Numberformat = "dddd m/d"
End With
Next

 

[Tom Ogilvy]

vval = Worksheets("Sheet1").Dropdowns("Drop Down 1").Value

The URL is fed to the web query as a string. So just concatenate the
variable part of the string

sURL = "http://www.myserver.com/myloc/" & vVal & ".htm"

as an example.

 

[Brian Vallelunga]

Thanks for this. It really helps.

Brian